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

Answers

  • ker9
    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
    Btrombler ✭✭✭

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

  • ker9
    ker9 ✭✭✭✭✭✭

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

  • Btrombler
    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
    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
    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
    ker9 ✭✭✭✭✭✭
    Answer ✓

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

  • Btrombler
    Btrombler ✭✭✭

    @ker9 you are awesome, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!