Sum cost between a date range

Hi,

I'm struggling with a forumula to sum the cost of items that are received between a range of dates.


So far I've tried this and it returns #NO MATCH

=SUMIFS([Working Budget]:[Working Budget], [Desired Receiving Date]:[Desired Receiving Date], >=DATE(2022, 9, 1), [Desired Receiving Date]:[Desired Receiving Date], <=DATE(2022, 10, 1))


and this, which returns #INVALID DATA:


=SUMIF(AND([Desired Receiving Date]:[Desired Receiving Date], @cell > DATE(2022, 9, 1), [Desired Receiving Date]:[Desired Receiving Date], @cell < DATE(2022, 10, 1)), $[Working Budget]:$[Working Budget])


image.png


What am I missing?

Answers

  • Kelly Moore
    Kelly Moore Community Champion

    Hey @Tara Factor

    Try adding an ISDATE to your SUMIFS formula

    =SUMIFS([Working Budget]:[Working Budget], [Desired Receiving Date]:[Desired Receiving Date], ISDATE(@cell), [Desired Receiving Date]:[Desired Receiving Date], >=DATE(2022, 9, 1), [Desired Receiving Date]:[Desired Receiving Date], <=DATE(2022, 10, 1))

    The ISDATE will help filter out rows that contain non-date values. Non-date values may confuse a formula that has date criteria

    Does this solve your issue?

    Kelly

  • Tara Factor
    Tara Factor ✭✭✭

    Hi Kelly,


    Sorry no luck. It still returned #NO MATCH. Any other ideas? You think it has something to do with blank cells in the date column?

  • Kelly Moore
    Kelly Moore Community Champion

    Hey Tara

    I thought those blank cells might be a problem, or any other non-date values, or errors in that column.

    To verify, your date column is formatted as a date? Are those date values manually entered?

    Let's try this:

    =SUMIFS([Working Budget]:[Working Budget], [Working Budget]:[Working Budget], ISNUMBER(@cell), [Desired Receiving Date]:[Desired Receiving Date], ISDATE(@cell), [Desired Receiving Date]:[Desired Receiving Date], @cell>=DATE(2022, 9, 1), [Desired Receiving Date]:[Desired Receiving Date], @cell<=DATE(2022, 10, 1))

  • Kelly Moore
    Kelly Moore Community Champion

    Also, are you trying for the Store Name or the Sum of the Working Budget? The formula above is the Sum of the Working Budget, between your dates.

  • Tara Factor
    Tara Factor ✭✭✭

    Hi Kelly,

    I figured it out. Your formula works. At the bottom of the sheet I had a "template" row that wasn't filled out which meant that the Working Budget column had a cell that said "#NO MATCH". Once I deleted that it worked.


    Thanks for your help!

  • Kelly Moore
    Kelly Moore Community Champion

    Glad you got it to work!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!