Combine IF formulas in one cell

Nathalie L.

Hello,

I have the following table (below) with a column ''amount of per diem used'' and a column ''number of days abroad''. The total amount of per diem will depend on whether the 40% rate applies, or the 70% rate or the full amount.

So, I would like to use a multiple formula in one cell that says:

if the box 40% per diem is checked, then the calculation is SUM(per diem used * no. of days * 0.4)

if the box 70% per diem is checked, then the calculation is SUM(per diem used * no. of days * 0.7)

if the box Full per diem is checked, then the calculation is SUM(per diem used * no. of days)

My formula is

=IF([40% per diem]@row = 1, SUM([Per diem used]@row * [No of days]@row * 0.4)), IF([70% per diem]@row = 1, SUM([Per diem used]@row * [No of days]@row * 0.7)), IF([Full per diem]@row = 1, SUM([Per diem used]@row * [No of days]@row))

It works if I use only one formula =IF([40% per diem]@row = 1, SUM([Per diem used]@row * [No of days]@row * 0.4)), but when combining the three formulas altogether as above, it doesn't work.

Many thanks for your help!

Nathalie

Paul Newcome

Try this (assuming there will always be one box checked):

=[Per diem used]@row * [No of days]@row * IF([40% per diem]@row = 1, 0.4, IF([70% per diem]@row = 1, 0.7, 1))

Nathalie L.

Excellent, it works, thanks so much!!

Paul Newcome

Happy to help. 👍️