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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • SoS | Dan Palenchar
    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?

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • MCorbin
    MCorbin 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

  • Irina Iatco
    Irina Iatco ✭✭
    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")


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!