Count If Formula to Count if NOT blank

Marie Eastman

I am trying to create rollup summaries in a large worksheet. For a couple of metrics I need I need to include or eclude certain things based on if a cell is blank or populated.

 

For example - I need to count something for a processor if the Auditor fields is blank. The formula below is working

=COUNTIFS(Risk:Risk, SSO4, Processor:Processor, SSO3, [RHB TO AUDITOR]:[RHB TO AUDITOR], "")

 

Next I need to count the same row for an Auditor if the Audiotr field is NOT blank.

Currently this is my formula - =COUNTIFS(Risk:Risk, SSO8, Auditor:Auditor, SSO$3)

but I need to add the additional criteria. I have tried this:

=COUNTIFS(Risk:Risk, SSO8, Auditor:Auditor, SSO$3, [RHB TO AUDITOR]:[RHB TO AUDITOR], "<>")

 

It is not working. Anyone know how I can get it to work?

 

 

 

 

Kate French

Hi Marie, 

 

Currently, there's not a way to check for "isn't blank" in a range for a COUNTIFS function. I've added your vote for this to look into this in future enhancements!

 

In the meantime, you might try adding one more column to the sheet, called "Blank?" or something similar. In the new column, add a formula to return the number 1 if the Auditor field is blank, and a 0 if it's not. That might look like this in row 1: =IF(ISBLANK([RHB TO AUDITOR]1), 1, 0)

 

Drag the new formula down to include all of the Auditor rows. Then in your COUNTIFS formula, reference your new column: =COUNTIFS(Risk:Risk, SSO8, Auditor: Auditor, SSO$3, [Blank?]:[Blank?], 0)

 

Let me know if you have any questions or if this doesn't work!