COUNTIFS formula that excludes items with a certain status

Kristine Fachet

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

You would need something like this...

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

Kristine Fachet

@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

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

@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 : )

sandee

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")

Sandee Murray

@Paul Newcome thanks! what date <> mean?

Sandee Murray

@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

@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.

Sandee Murray

@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

@Sandee Murray Here is the list of functions:

https://help.smartsheet.com/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.