# 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?

• ✭✭✭✭✭✭
Options

Hey @Tara Factor

=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

Kelly

• 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?

• ✭✭✭✭✭✭
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))

• ✭✭✭✭✭✭
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.

• 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.