Count IF , using "and", "not"

I am cross referencing 2 different sheets getting an error can someone help.

=COUNTIF({Munis Range 1}, "Facilities", {Contract Extensions Range 1}, NOT("Expired", "Closed"))

I want it to count if the first range is "Facilities" and if the second range is NOT "expired" or "Closed"


  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    Hi @LesahDee ,

    Give this a try.

    =COUNTIFS({Munis Range 1}, "Facilities", {Contract Extensions Range 1}, <> "Expired", {Contract Extensions Range 1}, <> "Closed")

    Hope this helps,


  • Hi

    thanks for responding I tried it and it says incorrect argument, UGH

  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @LesahDee ,

    Since you're trying to get data from two different sheets, it will be helpful if you can share which conditions belong to the different sheets.

    For example, if you're trying to get a count of all facilities from sheet 1 and all open contracts in sheet 2, they are two distinct values. You will need to establish a link between the two sheets to get the consolidated number of how many facilities contracts are still open.

    Alternatively, if you're trying to sum up the count of facilities in sheet 1 and the count of open contracts in sheet 2, you will need to use count if for facilities from sheet 1 + count if for open contracts from sheet 2.



    Reach out for any help on licenses, configuration, or training

  • =COUNTIF({Munis Range 1}, "Facilities", {Contract Extensions Range 1}, NOT("Expired", "Closed"))

    the Facilities belong to the Munis sheet and the Expired Closed belong to the Contract extension sheet. I am not combining the numbers from the 2 sheets , just using the conditions from two different sheets.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!