Formula construction
So I'm trying to count the number of records that are noncompliant in status column that does not include the number revoked in Flagged column. I'm getting "INVALID" result. Can you tell me where I went wrong?
=COUNTIFS({Status}, "noncompliant", {Flagged,<>"revoked"})
There are some blanks in the flagged column which is OK. I want everything counted, even the blanks except "revoked".
Now that I think about it, maybe this formula should include isblank???...🤔 I don't know, it gets so confusing to me.
Any help is greatly appreciated!
Best Answer

Try this...
=COUNTIFS(Status:Status, @cell = "Noncompliant", Flagged:Flagged, @cell <> "Revoked")
Answers

Hey @jgneely72151
Just clarifying, the {Status} column and the {Flagged} columns are on a different sheet than the sheet where this formula resides? I want to be sure we're using the correct syntax.
As written
=COUNTIFS({Status}, "noncompliant", {Flagged},<>"revoked")
cheers,
Kelly

So, both the status and flagged columns are on the same sheet that I'm referencing. I want a count of all of the records with a noncompliant status but I don't want to include the records with revokes from the flagged column. When I entered your formula, it actually worked but it only returned 1 record when I should have gotten 20.
Below is sample data of what my actual sheet looks like
If I filter the below for a total of noncompliant records, there is a total of 7 records with noncompliant but I don't want to count the revokes. Without the revokes, there should be 5. I need a formula to return 5 as I'm preparing metrics for a dashboard.

Try this...
=COUNTIFS(Status:Status, @cell = "Noncompliant", Flagged:Flagged, @cell <> "Revoked")

So I got the same results that Kelly provided. I should have 20 but it's returning 1. Does the blank cells have anything to do with the count?

I have a total of 34 records. With the revokes removed from the flagged column, I have 29 records. Of the 29 records, I also need to know how many has a noncompliant. It should be 20 records. This is all based off of me running filters on the sheet.
So the flagged column has 5 records tagged as revoked. Only 1 record has "other" and the rest are blank. Are the blanks affecting the formula? Because if that is the case, the formula that returned the 1 would be correct because it is looking at the record "other". I need the blanks to be counted as well or is that even possible??? Should I change the field properties to checkmark to count true/false??? So not good at this.

Give this one a go...
=COUNTIFS(Status:Status, @cell = "Noncompliant", Flagged:Flagged, OR(@cell = "", @cell <> "Revoked"))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!