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!

Kara Lumley

Hello Marie!

Just shared a sheet with you that my colleague Darren helped out with:

The formula works by counting everything where risk and processor have values, and subtracting everything where risk and processor have values and RHB TO AUDITOR is blank. This gives us the difference -- which tells us how many rows there are where RHB TO AUDITOR is not blank but the other values match.

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

I hope this helps!

Kara

Marie Eastman

Thank You Kara & Darren this worked! I really appreicate it. These automated counts eliminate alot of filtering every morning!