SUMIFS with date range

Btrombler

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)))

ker9

@Btrombler 6/31/24 is not a real date - is that the problem? Try 6/30/24?

ker9

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))

Btrombler

hello @ker9 I did try that as well, but unfortunately it's not working either :(

ker9

@Btrombler - is your date column formatted as a Date (not text)?

Btrombler

@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.

ker9

@Btrombler

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)))

Btrombler

@ker9

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.

ker9

@Btrombler 6/31/24 is not a real date - is that the problem? Try 6/30/24?

Btrombler

@ker9 you are awesome, thank you!