COUNTIFS formula that excludes items with a certain status

Kristine Fachet
edited 07/27/21 in Formulas and Functions

Hello! I want to create a graph that shows current Creative requests by department. I currently have a metrics sheet that is counting the total number of requests by department with the below formula, but I want it to exclude any projects where the status is "complete".

Here's the formula I currently have:

=COUNTIFS({Creative Services Tracker Requesting Dept}, Description@row)

The brackets are referencing another sheet: Creative Services Tracker = sheet name; Requesting Dept = column to count.

There is another column of the referenced sheet named "Status" that would have the project status to check against.

Thanks in advance for your help!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need something like this...

    =COUNTIFS({Creative Services Tracker Requesting Dept}, Description@row, {Other Sheet Status Column}, @cell <> "Complete")

  • @Paul Newcome that works, thank you! If I wanted to also exclude another status type, like "cancelled", would I repeat that last string, or would I write it differently?

    Here's what I ended up with, following your advice:

    =COUNTIFS({Creative Services Tracker Requesting Dept}, Description@row, {Marketing & Creative Services Tracker Status}, @cell <> "Complete")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could do that:

    =COUNTIFS({Creative Services Tracker Requesting Dept}, Description@row, {Marketing & Creative Services Tracker Status}, @cell <> "Complete", {Marketing & Creative Services Tracker Status}, @cell <> "Cancelled")

    Or my personal preference is to only list the range once and group the criteria together (useful for organizing super complex formulas)

    =COUNTIFS({Creative Services Tracker Requesting Dept}, Description@row, {Marketing & Creative Services Tracker Status}, AND(@cell <> "Complete", @cell <> "Cancelled"))

  • Sandee Murray
    edited 08/03/21

    @Paul Newcome and @Kristine Fachet

    Total newbie- but where do you put the formula? in the column you want the resulting data to appear? I am trying to set up a workflow that when a due date+1day is reached and the status isnt complete that it then updates the symbol in the heath column to the red stop sign from the green.

    The automation tool isn't working and I wonder if there is another way to set up automation and this thread makes me think there is : )


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sandee Murray The formula should go into whatever cell you want the output to be in. If you want a formula that outputs "Green" until Due Date is in the past with a status that is not complete, you would put something like this into your symbols column:

    =IF(AND(Status@row <> "Complete", [Due Date]@row < TODAY()), "Red", "Green")

  • @Paul Newcome Is a formula possible to achieve the following automation?

    Update the Health column to either a red, yellow, green, or gray circle if:

    • Status is future date= gray
    • Status is complete= green
    • Status is 'in progress' and the due data is in the future= green
    • The due date is passed but the completed date is blank OR the status is In process and it is past the due date= Red

    I might be asking alot of the formula functionality. : )

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sandee Murray "<>" means "not equal to" it is basically the opposite of "=".

    As for your other question, it is very doable, but... Since it is not related to the original post which is regarding a COUNTIFS, could you please start a new post with your question? You are more than welcome to @mention me there, and I will stop by to help out. Having it separated since it is different from the original post means that it will be easier to find via searches for others that are encountering the same challenge.

  • @Paul Newcome will do!

    Do you if there is a reference sheet that gives a definition for each of the functions and symbols used in the formulas?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sandee Murray Here is the list of functions:

    As for argument symbols, they are going to be the usual:

    = Equals

    <> Not Equal To

    < Less Than

    > Greater Than

    <= Less Than or Equal To

    >= Greater Than or Equal To

    and then the usual math functions of + - * /

    I may be missing some, but that's the best list I can come up with off the top of my head.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!