Countifs with Multiple Criteria in the Same Range

Jill Youngberg
Jill Youngberg ✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to create a formula to count the number of projects assigned to one person based on the phase of a project. My criteria is that is NOT one of the following phases: Construction, Completed, Punch, On Hold. I know I need to use <> to create the "not equal to" but for some reason, I am missing something to make it work for all of the phases I am trying to exclude.



  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 01/28/19

    I might need a bit more info, but if you are only looking for specific phase or phases, you can just ignore what you are not looking for. It might make your nested formula easier to work with.  


    =COUNTIFS([Status1]:[Status1], "On Schedule", [Assigned To]:[Assigned To], "Walt D")


  • Jill Youngberg

    I am not sure that works, but could be wrong. There are 5 statuses that I am trying to count all in the same formula. I thought I had been able to include multiple status in one formula, but I seem to have forgotten the correct way to do so...

  • Brian W
    Brian W ✭✭
    edited 01/28/19

    Try this:

    =COUNTIFS([Assigned To]:[Assigned To], "Employee", [Phase]:[Phase], NOT(OR(@cell = "Construction", @cell = "Completed", @cell = "Punch", @cell = "On Hold")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!