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.
Best 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.
Answers
-
Your syntax for the SUMIFS is incorrect. The range to sum comes first in the SUMIFS.
-
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)))
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!