Question

Help with IF formula with checkboxes

Forms Version 11

Updated October 19, 2022

asked on October 17, 2022

I would like the Total Meal Deduction field to calculate based on if the Breakfast, Lunch, or Dinner is checked. The value of each meal is in the highlighted fields above, which populate from a table. For example, if Breakfast and Lunch are checked then the Total Meal Deduction should calculate to $30. Is anyone able to help me will the IF formula to make this work?

0 0

Answer

SELECTED ANSWER

replied on October 19, 2022

Hi Vanessa, it would look like this that you would put in the Total Meal Deduction in the Collection.

You will need to use the Tokens from your Collections for the one's in Bold

=SUM(IF(INDEX(CollName.CollCheckbox.Breakfast,ROW())=TRUE,VALUE(GSA_Breakfast),0),IF(INDEX(CollName.CollCheckbox.Lunch,ROW())=TRUE,VALUE(GSA_Lunch),0),IF(INDEX(CollName.CollCheckbox.Dinner,ROW())=TRUE,VALUE(GSA_Dinner),0))

1 0

Replies

replied on October 19, 2022

Just as an fyi, When using Collections I typically put a border in to separate the rows.

I would add a CSS Class to the Collection container such as CollectBorder and then put this in the CSS area

.CollectBorder .rpx {border-style:solid;border-color:darkred;border-width:2px 0px 0px 0px;}

It will give you a little separator as you add rows to look like this

2 0

replied on October 19, 2022

That worked great! Thank you so much for all your help Steve!

1 0

replied on October 18, 2022

Thank you for the tip, but I was unable to get it to work.

0 0

replied on October 18, 2022 • Show version history

Assuming your checkbox and table variable names follow the name of the fields, you should just be able to replace my Table name with yours, and this should work

=SUM(IF(Checkbox.Breakfast=TRUE,TestPerDiemTable.Breakfast,0),IF(Checkbox.Lunch=TRUE,TestPerDiemTable.Lunch,0),IF(Checkbox.Dinner=TRUE,TestPerDiemTable.Dinner,0))

0 0

View 3 previous replies

replied on October 18, 2022

I am still unable to get it to work. Here is the formula I made with your help from above, =SUM(IF(FirstDay_MealsProvided.Breakfast=TRUE,GSA_Breakfast,0),IF(FirstDay_MealsProvided.Lunch=TRUE,GSA_Lunch,0),IF(FirstDay_MealsProvided.Dinner=TRUE,GSA_Dinner,0))

When I use each IF formula independently they work (ex. =IF(FirstDay_MealsProvided.Breakfast=TRUE,GSA_Breakfast,0)). It's when I try combining the IFs into a SUM formula they don't work.

0 0

replied on October 18, 2022

Are your GSA_fields, number or single line fields?

0 0

replied on October 19, 2022

If your GSA fields are Single line, you need to add VALUE(GSA_fieldname) as in the formula below. When using a number in a calculation, if that number is in a single line field, LF treats it like text, adding VALUE tells LF to treat it as a number.

=SUM(IF(Checkbox.Breakfast=TRUE,VALUE(GSA_Breakfast),0),IF(Checkbox.Lunch=TRUE,VALUE(GSA_Lunch),0),IF(Checkbox.Dinner=TRUE,VALUE(GSA_Dinner),0))

2 0

replied on October 19, 2022

Thank you Steve! It worked great!

I have the same fields in a collection for employees to add additional days of travel. Would you be able to tell me what I need to add to this formula to make it work in a collection? I have bolded the variables that are in a collection.

=SUM(IF(Other_Days_Collection.OtherTravelDay_MealsProvided.Breakfast=TRUE,VALUE(GSA_Breakfast),0),IF(Other_Days_Collection.OtherTravelDay_MealsProvided.Lunch=TRUE,VALUE(GSA_Lunch),0),IF(Other_Days_Collection.OtherTravelDay_MealsProvided.Dinner=TRUE,VALUE(GSA_Dinner),0))

0 0

replied on October 19, 2022

Can you send a screenshoot of your form, is the Total Meal Deduction inside the Collection as well?

0 0

replied on October 19, 2022

The area highlighted is a collection for the employee to add as many days as needed between the first and last day of travel. I was able to get the first and last day of travel to work with the formula you previously provided me. However the Total Meal Deduction inside the collection needs something extra to make it work properly. I really appreciate all your help Steve.

0 0

You are not allowed to follow up in this post.

Question

Question

Help with IF formula with checkboxes

Answer

Replies

Sign in to reply to this post.