Search code examples
excelperformanceloopsexcel-formula

Excel: More efficient way of checking multiple AND in an OR function

I have following code for a checksum in my excel sheet:

=If(Or(And(E5="Other";G5="n.a.");And(E10="Other";G10="n.a."); ANND );"Missing entry";"All good")

Basically if the user selected "Other" in a cell, the user is forced to change the default answer "n.a." to an actual comment.

ANND represents the AND conditions for the 50+ more rows where I would have to check if the condition applies. This naturally slows down excel, so my question is if there is a faster way to check the conditions.

I would prefer to accomplish this without VBA and loops as the user might not feel comfortable using them.

Solution

  • From comment to answer, this is what OP could use:

    =IF(COUNTIFS(E:E;"Other";G:G;"n.a.");"Missing entry";"All good")