Sum cost between a date range

Tara Factor

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?

Kelly Moore

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

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

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

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

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

Glad you got it to work!