SUMIF plus COUNTIF

ElizabethOwen

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.

Paul Newcome

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.

Paul Newcome

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

https://help.smartsheet.com/function/sumifs

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

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.

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