# SUMIF plus COUNTIF

Options
✭✭✭✭

Hi all,

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:

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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!