Formula construction

jgneely72151

So I'm trying to count the number of records that are non-compliant 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}, "non-compliant", {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!

Paul Newcome

Try this...

=COUNTIFS(Status:Status, @cell = "Non-compliant", Flagged:Flagged, @cell <> "Revoked")

Kelly Moore

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}, "non-compliant", {Flagged},<>"revoked")

cheers,

Kelly

jgneely72151

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 non-compliant 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 non-compliant records, there is a total of 7 records with non-compliant 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.

Paul Newcome

Try this...

=COUNTIFS(Status:Status, @cell = "Non-compliant", Flagged:Flagged, @cell <> "Revoked")

jgneely72151

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?

jgneely72151

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 non-compliant. 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.

Paul Newcome

Give this one a go...

=COUNTIFS(Status:Status, @cell = "Non-compliant", Flagged:Flagged, OR(@cell = "", @cell <> "Revoked"))