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.
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!
Answers
-
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!
-
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.
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!
-
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
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!