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
Check out the Formula Handbook template!