Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Count If Formula to Count if NOT blank

Options
Marie Eastman
Marie Eastman ✭✭✭
edited 12/09/19 in Archived 2016 Posts

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 Smile

=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?

 

 

 

 

Tags:

Comments

  • Kate French
    Options

    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
    Options

    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
    Options

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

This discussion has been closed.