How do I use multiple IF statements with multiple criteria?

Daniel S.

I am new and trying to write an IF statement with multiple criteria. The sheet will accept my formula, it will only deliver the result in the cell for the 1st two statements. I read a ton of remarks from others in the community, and I just can't find where I'm making the mistake. Any help at all would be much appreciated.

I have a "Water Main Failure" sheet that is populated by a form used by field workers. I've been asked to add a "Cost" for failures of certain size water mains based on their "Nature of Failure".

I need to provide a cost for 6,8,10, and 12 inch. water mains based on if they failed b/c of a "straight break" or a "split. I have another sheet that provides the cost, but I chose to just input the cost for each instead of pulling from another sheet.

Here is the formula I have, and a screenshot of the sheet.

=IF([Main Size]@row = "6", IF([Nature of Failure]@row = "Straight Break", "$800",

IF([Main Size]@row = "6", IF([Nature of Failure]@row = "Split", "$2000",

IF([Main Size]@row = "8", IF([Nature of Failure]@row = "Straight Break", "$790",

IF([Main Size]@row = "8", IF([Nature of Failure]@row = "Split", "$2500",

IF([Main Size]@row = "10", IF([Nature of Failure]@row = "Straight Break", "$880",

IF([Main Size]@row = "10", IF([Nature of Failure]@row = "Split", "$2300",

IF([Main Size]@row = "12", IF([Nature of Failure]@row = "Straight Break", "$920",

IF([Main Size]@row = "12", IF([Nature of Failure]@row = "Split", "$2700"))))))))))))))))

Does my formula need to be an IF(AND, IF(OR? I am at a loss right now.

Thanks ahead of time for any help!!!

=Chris Palmer

Hi @Daniel S.

Something like this perhaps?

=IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Straight Break"), "$800",
IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Split"), "$2000",
IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Straight Break"), "$790",
IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Split"), "$2500",
IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Straight Break"), "$880",
IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Split"), "$2300",
IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Straight Break"), "$920",
IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Split"), "$2700",
""))))))))

Brian Wilson DC

@Daniel S. What you want to use is a combined IF(AND statement. Essentially, I have placed the formula you need below to qualify all of the conditions you listed above. Essentially, it reads as follows:

IF "Main Size" = 6 and "Nature of Failure" = Straight Break then the cost is $800. If that is both not true move on to the next pairing of conditions. "Main Size" = 6 and "Nature of Failure" = "Split". If that is true then $2000. If that is not true move on to the next set, etc.

If you place the following formula in each cell of your cost:

=IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Straight Break"), "$800", IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Split"), "$2000", IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Straight Break"), "$790", IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Split"), "$2500", IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Straight Break"), "$880", IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Split"), "$2300", IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Straight Break"), "$920", IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Split"), "$2700"))))))))

Here is what it looks like in the table below. You will see the way I have written it it leaves a row that does not meet any conditions with a value of 0.

I hope this helps!

-Brian

=Chris Palmer

Hi @Daniel S.

Something like this perhaps?

=IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Straight Break"), "$800",
IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Split"), "$2000",
IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Straight Break"), "$790",
IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Split"), "$2500",
IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Straight Break"), "$880",
IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Split"), "$2300",
IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Straight Break"), "$920",
IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Split"), "$2700",
""))))))))

Brian Wilson DC

@Daniel S. What you want to use is a combined IF(AND statement. Essentially, I have placed the formula you need below to qualify all of the conditions you listed above. Essentially, it reads as follows:

IF "Main Size" = 6 and "Nature of Failure" = Straight Break then the cost is $800. If that is both not true move on to the next pairing of conditions. "Main Size" = 6 and "Nature of Failure" = "Split". If that is true then $2000. If that is not true move on to the next set, etc.

If you place the following formula in each cell of your cost:

=IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Straight Break"), "$800", IF(AND([Main Size]@row = "6", [Nature of Failure]@row = "Split"), "$2000", IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Straight Break"), "$790", IF(AND([Main Size]@row = "8", [Nature of Failure]@row = "Split"), "$2500", IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Straight Break"), "$880", IF(AND([Main Size]@row = "10", [Nature of Failure]@row = "Split"), "$2300", IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Straight Break"), "$920", IF(AND([Main Size]@row = "12", [Nature of Failure]@row = "Split"), "$2700"))))))))

Here is what it looks like in the table below. You will see the way I have written it it leaves a row that does not meet any conditions with a value of 0.

I hope this helps!

-Brian

Daniel S.

That worked perfectly and thank you for the explanation as well!

Brian Wilson DC

You're welcome! Have a fantastic day!