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

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:

  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]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#rows.com
#natural language processing for spreadsheets
#generative AI for data analysis
#Excel compatibility
#row zero
#Excel alternatives
#COUNTIFS
#strings
#S1
#S2
#condition
#column
#ISBLANK
#A:A
#B:B
#blanks
#wildcard
#evaluate
#comparison