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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!