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!
Best Answers
-
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. 👍️
Answers
-
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?
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
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
-
@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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
