IF(AND(OR? Formula

AidenM

Hello - I am trying to write a formula for the following functionality: I have one column that is a checkbox, one column that is either "Standard" or "Non-Standard", and I want to calculate if the checkbox column is checked ([Needs Replacement]@row=1), AND if the [Room Type]@row="Standard" - then do this calculation ([Hours Needed]@row * rate 1). If the checkbox is checked AND the [Room Type]@row="Non-Standard", then do this calculation ([Hours Needed]@row * rate 2) - otherwise return nothing (""). I tried using a nested IF(AND formula, but it gives me an "Incorrect Argument Set" error. Here is my formula:

=IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Standard", SUM(95 * [Hours Needed]@row * 0.3), IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Non-Standard", SUM(115 * [Hours Needed]@row * 0.3, "")))))

I did try to remove the 2nd IF(AND, but with the same error result - can anyone help me?

Carson Penticuff

I think I see what you are trying to do, give this a try:

=IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Standard"), 95 * [Hours Needed]@row * 0.3, IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Non-Standard"), 115 * [Hours Needed]@row * 0.3, ""))

Carson Penticuff

I think I see what you are trying to do, give this a try:

=IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Standard"), 95 * [Hours Needed]@row * 0.3, IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Non-Standard"), 115 * [Hours Needed]@row * 0.3, ""))

AidenM

Ahh - I see what I did wrong there. It worked! Thanks for the assist.

Carson Penticuff

👍 I'm glad its working!