You are viewing limited content. For full access, please sign in.

Question

Question

Exclude values in hidden tables from a field calculation

asked on June 26 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
0 0

Answer

SELECTED ANSWER
replied on June 26

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

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

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

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.