2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Selectively Ignore Conditions in COUNTIFS

Our take

If you're looking to enhance your COUNTIFS function by selectively ignoring conditions based on whether a specific string is blank, there’s a more effective approach. Your goal is to count occurrences of a string (S1) in one column (A) while excluding instances where another column (B) matches a different string (S2), but only if S2 is not blank. To achieve this, consider using a formula that incorporates conditional logic, such as: =COUNTIFS(A:A, S1, B:B, IF(ISBLANK(S2), "*", "<>"&S2)). This

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:

  1. =COUNTIFS(A:A,S1,B:B,"<>"&S2+ISBLANK(S2))
  2. =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?

submitted by /u/littleseizure
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article

Tagged with