SUMIFS with date ranges
Hello,
I'm trying to sum the values (Annual Billing Amount) for anything that falls between 2/22/22 and today but I can't get my formula to work. Not sure what I'm doing incorrectly.
=SUMIFS({Annual Billing Amount}:{Annual Billing Amount}, {Date to Deactivate}:{Date to Deactivate}, AND(>=DATE(2022, 2, 22), <=TODAY()))
Answers
-
Try adding another criteria rather than using the AND function.
=SUMIFS([Annual Billing Amount]:[Annual Billing Amount], [Date to Deactivate]:[Date to Deactivate], >=DATE(2022, 2, 22), [Date to Deactivate]:[Date to Deactivate], <TODAY())
In my example sheet I tested in, I didn't use cell references, simply the other columns in the sheet. So not sure on your curly braces there.
-
Thanks so much for your response. I tried your formula (uses curly braces instead of brackets), I still got an unparsable error. I have used cross-sheet references before without an issue so not sure what's happening here.
-
Make sure your Date to Deactivate column is set to Date.
When using curly braces you only need the one reference.
=SUMIFS({Annual Billing Amount}, {Date to Deactivate}, >=DATE(2022, 2, 22), {Date to Deactivate}, <TODAY())
-
That worked! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!