SUMIF plus COUNTIF

Hi all,

I asked another question in this thread.

I now want to take a step further. I would like for the formula to sum the amounts in the SAR Loss column of my other sheet only if the Date Filed is between <Date 1> and <Date 2> AND the subcategory type matches @row.

=SUMIFS({Date SAR Filed}, >=DATE(2024, 1, 1), {Date SAR Filed}, <=DATE(2024, 1, 31), {Subcategory}, CONTAINS(Finding@row, @cell), {SAR Loss})

So in my last one I was looking for a count. Now I am looking to add up $$$ if the Dates Match and the Subcategory matches.


Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your syntax for the SUMIFS is incorrect. The range to sum comes first in the SUMIFS.



    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

  • ElizabethOwen
    ElizabethOwen ✭✭✭✭

    Thanks Paul,

    Unfortunately, I don't think I am getting it. I've tried the following:

    =SUMIFS({SAR Loss}:{SAR Loss}, IFS({Date SAR Filed}, >=DATE(2024, 1, 1), {Date SAR Filed}, <=DATE(2024, 1, 31), {Subcategory}, CONTAINS(Finding@row, @cell)))

    =SUMIFS({SAR Loss}:{SAR Loss}, COUNTIFS({Date SAR Filed}, >=DATE(2024, 1, 1), {Date SAR Filed}, <=DATE(2024, 1, 31, {Subcategory}, CONTAINS(Finding@row, @cell)))

    =SUMIFS({SAR Loss}, COUNTIFS({Date SAR Filed}, >=DATE(2024, 1, 1), {Date SAR Filed}, <=DATE(2024, 1, 31), {Subcategory}, CONTAINS(Finding@row, @cell)))

    =SUMIFS({SAR Loss}, IFS({Date SAR Filed}, >=DATE(2024, 1, 1), {Date SAR Filed}, <=DATE(2024, 1, 31), {Subcategory}, CONTAINS(Finding@row, @cell)))

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

    The formula from your original post was the closest. You just needed to move the {Range to sum} from being the last field in the sumifs to the first field.

    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

  • ElizabethOwen
    ElizabethOwen ✭✭✭✭

    Changed it to the following and it appears to be working, thank you!!

    =SUMIFS({SAR Loss}, {Date SAR Filed}, >=DATE(2024, 1, 1), {Date SAR Filed}, <=DATE(2024, 1, 31), {Subcategory}, CONTAINS(Finding@row, @cell))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!