# Sumifs Question

I have this formula with sumifs and I am trying to add "and is not "

=COUNTIFS({Marketing WIP - Bone Pile Range 1}, "christina", {Marketing WIP - Bone Pile Range 2}, "PROPOSAL") + COUNTIFS({Marketing WIP - Bone Pile Range 1}, "christina", {Marketing WIP - Bone Pile Range 2}, "QUALIFICATIONS")

I want to add and range is not "canceled" . How do I do that ? Thanks much!

• ✭✭✭✭✭✭

You will need to add the additional range/criteria to BOTH of your COUNTIFS. You can also save yourself a little hassle and help the sheet work more efficiently by using an OR statement which would allow you to use a single COUNTIFS and only enter each range once instead of twice just to add them together.

=COUNTIFS({Marketing WIP - Bone Pile Range 1}, "Jennifer tulipani", {Marketing WIP - Bone Pile Range 2}, OR(@cell = "PROPOSAL", @cell = "QUALIFICATIONS"), {Status}, <> "Cancelled")

This would be a stand-alone and not require a second COUNTIFS to be added to it.

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭✭✭

Do you want this to apply to each of the individual COUNTIFS statements?

Also, which range out of the 2 are you going to look at for non "canceled" values?

#### 👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

• Overachievers Alumni

Is "Canceled" another column?

Assuming it's a "status" column, I'd do this:

=COUNTIFS({Marketing WIP - Bone Pile Range 1}, "christina", {Marketing WIP - Bone Pile Range 2}, "PROPOSAL",{Status},<>"Canceled") + COUNTIFS({Marketing WIP - Bone Pile Range 1}, "christina", {Marketing WIP - Bone Pile Range 2}, "QUALIFICATIONS",{Status},<>"Canceled")

If that doesn't work, can you tell me where you find the "Canceled" indicator and I'll see if I can help refine it

• edited 08/18/20

@MCorbin It's a status column indeed and it contains different statuses including "cancelled". I am trying to sum everything except for when the status is "cancelled". I tried the formula and it didn't make a difference in the results so I am assuming something is missing. I am attaching a screenshot for your review.

This is the formula I just used : =COUNTIFS({Marketing WIP - Bone Pile Range 1}, "Jennifer tulipani", {Marketing WIP - Bone Pile Range 2}, "PROPOSAL") + COUNTIFS({Marketing WIP - Bone Pile Range 1}, "Jennifer tulipani", {Marketing WIP - Bone Pile Range 2}, "QUALIFICATIONS", {Status}, <>"Cancelled")

• ✭✭✭✭✭✭

You will need to add the additional range/criteria to BOTH of your COUNTIFS. You can also save yourself a little hassle and help the sheet work more efficiently by using an OR statement which would allow you to use a single COUNTIFS and only enter each range once instead of twice just to add them together.

=COUNTIFS({Marketing WIP - Bone Pile Range 1}, "Jennifer tulipani", {Marketing WIP - Bone Pile Range 2}, OR(@cell = "PROPOSAL", @cell = "QUALIFICATIONS"), {Status}, <> "Cancelled")

This would be a stand-alone and not require a second COUNTIFS to be added to it.

• Than you so much, Paul! It worked.

• ✭✭✭✭✭✭