Excluding two values for Sheet Summary Formula

EmmaSob

Hi all,

I was wondering whether you might be able to help. I have a column "Status" and I would like to write a formula for a sheet summary field that counts the number of status' that are anything other than "On Hold" and "Complete".

I've tried various COUNTIFs but none seem to be working! Any help on this would be great!

Thanks,

Emma

Andrée Starå

Hi @EmmaSob

I hope you're well and safe!

Try something like this.

=COUNTIFS(Status:Status, AND(@cell <> "On Hold", @cell <> "Complete"))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @EmmaSob

I hope you're well and safe!

Try something like this.

=COUNTIFS(Status:Status, AND(@cell <> "On Hold", @cell <> "Complete"))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

EmmaSob

https://community.smartsheet.com/discussion/comment/316004#Comment_316004

Thank you so much! It worked!! Really appreciate your help.

Emma

Andrée Starå

@EmmaSob

Excellent!

You're more than welcome!

EmmaSob

I've now tried this fix which has worked for one of my fields but when I try and use it in conjunction with counting blank cells it doesn't seem to work:

I am trying to count the number of blank fields in a column (Request Country) whilst at the same time excluding rows with the status of "Closed", "On Hold" and "Rejected". I am trying this formula but it doesn't seem to be working:

=COUNTIFS([Request Country]:[Request Country], ISBLANK(@cell)), Status:Status, AND(@cell <> "On Hold", @cell <> "Closed", @cell <> "Rejected"))

Where am I going wrong?

Andrée Starå

@EmmaSob

Try something like this.

=
COUNTIFS([Request Country]:[Request Country], "", Status:Status, 
AND(@cell <> "On Hold", @cell <> "Closed", @cell <> "Rejected"))

Did it work?

EmmaSob

https://community.smartsheet.com/discussion/comment/316014#Comment_316014

Really weirdly, it is saying there are 3 fields that are blank when using that formula, but when I use the filter using the same parameters (ie blank with a status of anything other than closed, rejected or on hold it says I should have 0. I can't work out what would be causing it!

Andrée Starå

@EmmaSob

Strange!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)