Share via

ignore/exclude empty cells from IF function that also has AND function

Anonymous
2024-07-31T19:51:28+00:00

this is my current formula: =IF(AND(L23=J27:U27), "PASS", "FAIL")

I need the formula to ignore when all the cells are blank. I also need the formula to ignore a blank cell when the rest are all "P"

note: I dont have a "Developer" option.

L23=P

26 J K L M N O P Q R S T U Sample Pass?
27 P P P P P P P P P P P P PASS
28 P P P P P P P P P P P FAIL
29 P X P P P P P P P P P FAIL
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-08-01T12:46:24+00:00

    THIS WORKED! thank you again

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102K Reputation points Volunteer Moderator
    2024-07-31T23:05:33+00:00

    Hi,

    Enter this formula in cell V26

    =IF(COUNTBLANK(J26:U26)=COUNTA(J26:U26),"",IF(COUNTIF(J26:U26,$L$23)=COUNTA(J26:U26),"Pass","Fail"))

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2024-07-31T20:31:19+00:00

    Like this:

    =IF(COUNTA(J27:U27)=0, "", IF(COUNTIFS(J27:U27, "<>P", J27:U27, "<>"), "FAIL", "PASS"))

    Was this answer helpful?

    0 comments
  4. Anonymous
    2024-07-31T20:23:25+00:00

    =IF(COUNTIFS(J27:U27, "<>P", J27:U27, "<>"), "FAIL", "PASS") WORK GREAT!

    IS THERE AWAY THAT THE FORMULA IN THAT CELL CAN ALSO NOT FORMULATE IF ALL THE CELLS ARE BLANK?

    Was this answer helpful?

    0 comments
  5. HansV 462.6K Reputation points
    2024-07-31T20:08:35+00:00

    Try

    =IF(COUNTIF(J27:U27, "X"), "FAIL", "PASS")

    or

    =IF(COUNTIFS(J27:U27, "<>P", J27:U27, "<>"), "FAIL", "PASS")

    Was this answer helpful?

    0 comments