Sum cost between a date range

Options

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



What am I missing?

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    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 ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    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
    Options

    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 ✭✭✭✭✭✭
    Options

    Glad you got it to work!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!