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!

Find more posts tagged with

Accepted answers

Paul Newcome

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.

Paul Newcome

Happy to help. 👍️

All comments

SoS | Dan Palenchar

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?

MCorbin

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

Irina Iatco

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

Paul Newcome

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

Irina Iatco

https://community.smartsheet.com/discussion/comment/260105#Comment_260105

Than you so much, Paul! It worked.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.