Exclude values in hidden tables from a field calculation

asked on June 26, 2019 • Show version history

I've found answers to similar questions using SUMIFS, but not sure how to apply it to my case. Basically, I only want to include values from shown tables (not hidden tables) in a field calculation.

I have a form that allows the user to enter leave time based on days and/or hours. I have a checkboxes field, Vacation_DaysOrHours, with checkboxes for Days and Hours. Clicking on the Days checkbox shows the Leave_Days_Table table. Clicking on the Hours checkbox shows the Leave_Hours_Table table. I have a total Leave Hours field that calculates the sum of all hours entered in both tables. If a user enters hours in the Hours table, and then unchecks the Hours checkbox to hide it, I don't want those hours in the hidden table to be counted.

The current calculation for the total "Leave Hours" field is as follows:

=SUM(Leave_Days_Table.Hours)+SUM(Leave_Hours_Table.Leave_Hours)

I tried the following with no success:

=SUM(SUMIF(Vacation_DaysOrHours.Hours,"=True",Leave_Hours_Table.Hours))+SUM(SUMIF(Vacation_DaysOrHours.Days,"=True",Leave_Days_Table.Hours))

Calc_based_on_shown_tables.png (19.11 KB)

| Download

0 0

SELECTED ANSWER

replied on June 26, 2019

You're close, but SUMIF only works for comparing values in the table.

What you want is a standard IF

IF({condition},{true value},{false value})

IF(Vacation_DaysOrHours.Hours,Leave_Hours_Table.Hours,0)

Vacation_DaysOrHours.Hours should return TRUE if it is checked, and FALSE if not

1 0

replied on June 27, 2019

Ok, I was overthinking it. Thanks, Jason, for setting me straight.

I got it to work using the following code (in case anyone else is curious):

=IF(Vacation_DaysOrHours.Days,SUM(Leave_Days_Table.Hours),0)+IF(Vacation_DaysOrHours.Hours,SUM(Leave_Hours_Table.Hours),0)

Vacation_DaysOrHours is the checkboxes field with Days and Hours for the checkboxes.

0 0

Question

Question

Exclude values in hidden tables from a field calculation

Answer

Replies

Sign in to reply to this post.