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

Tags:

Answers

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    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.

  • deb_63_hydracor
    deb_63_hydracor ✭✭✭✭✭✭

    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.

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

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

  • deb_63_hydracor
    deb_63_hydracor ✭✭✭✭✭✭

    That worked! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!