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

Discussion

Discussion

How to calculate per diem cost based on number of meals allowed

Forms
Updated September 16, 2024
posted on September 13, 2024

Trying to craft a formula to calculate the total per diem cost based on the meals allowed. This table gets populated with whether or not meals are allowed on a travel day depending on the departure and return dates of a trip, with 1 meaning that meal is allowed:

In the Total Per Diem column, I've tried variations on this:

=SUMIF(depDateMealsTbl.depBreakfast=1, 16, (depDateMealsTbl.depLunch=1, 19, (depDateMealsTbl.depDinner=1, 28)))

Looking for a result of 47 in the Total Per Diem column, since lunch (19) and dinner (28) are populated with a 1.

Please help, and thank you.

0 0
replied on September 16, 2024

Thank you for the response. Looks like I wasn't clear - I need the calculation to take place if any of the meal fields are populated with a 1. The 1 has to remain because it is then used in a subsequent calculation.

0 0
replied on September 16, 2024

I think I understand. You would keep the 1 or 0. Multiply the amount for the given meal by either 1 or 0, then sum the three meals together. If they're all 0, the total amount will be 0.

=SUM(
INDEX(Departure_Date_Meals.Breakfast,ROW())*16,
INDEX(Departure_Date_Meals.Lunch,ROW())*19,
INDEX(Departure_Date_Meals.Dinner,ROW())*28
)

 

2 0
replied on September 16, 2024

After thinking about it in the interim, that's exactly where I ended up! Thank you so much for setting me on the right path.

1 0
replied on September 16, 2024 • Show version history

What if instead of SUMIF you sum the products of each meal?

Sum(depDateMealsTbl.depBreakfast*16, depDateMealsTbl.depLunch *19, depDateMealsTbl.depDinner *28)

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

Sign in to reply to this post.

Details
Related Posts
Loading ...