Formula construction

jgneely72151 ✭✭✭✭✭
edited 12/08/21 in Formulas and Functions

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


  • Kelly Moore
    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")



  • jgneely72151
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this...

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

  • jgneely72151
    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
    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
    Paul Newcome ✭✭✭✭✭✭

    Give this one a go...

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!