Countifs with Multiple Criteria in the Same Range

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.

Find more posts tagged with

Formulas

Comments

Nic Larsen

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.

Example:

=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

Try this:

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…