Formula construction
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!
Best Answer
-
Try this...
=COUNTIFS(Status:Status, @cell = "Non-compliant", 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}, "non-compliant", {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 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.
-
Try this...
=COUNTIFS(Status:Status, @cell = "Non-compliant", 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 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.
-
Give this one a go...
=COUNTIFS(Status:Status, @cell = "Non-compliant", 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!