Selectively Ignore Conditions in COUNTIFS
I am trying to use a COUNTIFS function to count in how many cells a string(S1) occurs in a column(A). I am also trying to ignore an instance if a different column(B) in the row equals a different string(S2). To do this I have:
=COUNTIFS(A:A,S1,B:B,"<>"&S2)
This works great. The problem is I only want to evaluate the second condition if S2 is not blank. If S2 is blank I want that entire condition to be transparent. To do this I have tried:
- =COUNTIFS(A:A,S1,B:B,"<>"&S2+ISBLANK(S2))
- =COUNTIFS(A:A,S1,B:B,IF(ISBLANK(S2,"*","<>"&S2))
The intent of 1 is to check if B is not equal to S2 AND if S2 is blank. If S2 is blank that check and therefore the entire second condition should return TRUE, which would make condition 2 not relevant for the COUNTIFS. Instead when blank I get the correct count, but when not blank I always return 0.
The intent of 2 is to compare the B range to "<>"&S2 when the IF(ISBLANK(S2)) statement is false, and when true compare the B range to some wildcard that will pass literally anything. In this case when S2 is not blank I get the correct value, but when S2 is blank I return an incorrect value.
I don't understand why either of these don't work. What's the correct way to do this?
[link] [comments]
Want to read more?
Check out the full article on the original site