SUMIFS with date range
Hello
I am trying to get a sum result based on a date range, but for some reason it is returning 0, any thoughts?
=SUMIFS(Amount:Amount, [Date Billed]:[Date Billed], AND(@cell <= DATE (2024, 6, 31), @cell > DATE(2024, 3, 31)))
Best Answer
-
@Btrombler 6/31/24 is not a real date - is that the problem? Try 6/30/24?
Answers
-
Hi @Btrombler
See if this helps you, presuming you want the total for the column Amount where Date Billed is within the specified date range
=SUMIFS(Amount:Amount, [Date Billed]:[Date Billed], <=DATE(2024, 6, 31), [Date Billed]:[Date Billed], >DATE(2024, 3, 31))
-
hello @ker9 I did try that as well, but unfortunately it's not working either :(
-
@Btrombler - is your date column formatted as a Date (not text)?
-
@ker9 that worked thanks so much! By chance, do you happen to know why the cell version isn't working? I've used that in the past without issue, so kinda scratching my head.
-
Using @Cell, may be a a little faster
=SUMIFS(Amount:Amount, [Date Billed]:[Date Billed], AND(@cell > DATE (2024, 3, 31), @cell <= DATE(2024, 6, 3)))
-
Thank you!
Another weird thing I noticed that I would appreciate your advise on. When I keep the formula as such (below), it seems to include those two amounts listed as dates 7/1/24 (even though I would expect to stop at 6/31/24 dates based on the formula). When I change those dates (to test) to 6/31/24, it removes them from the calculation. This makes 0 sense to me
=SUMIFS(Amount:Amount, [Date Billed]:[Date Billed], AND(@cell > DATE (2024, 3, 31), @cell <= DATE(2024, 6, 31)))
I did a test and changed the formula to this:
=SUMIFS(Amount:Amount, [Date Billed]:[Date Billed], AND(@cell > DATE (2024, 3, 31), @cell < DATE(2024, 6, 31)))
But it then it works as expected and only captures 6/30/34 and below.
How can I ensure it incldues 6/31/24 but not the 7/1/24? Unsure what the issue is.
-
@Btrombler 6/31/24 is not a real date - is that the problem? Try 6/30/24?
-
@ker9 you are awesome, thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!