DATEVALUE and using Cell Referencing

[shaun86]

I am trying to have a cell count the number of cells within a range to find how many have dates that fall between specific dates but getting the #VALUE ! error. My formula is:

=SUMPRODUCT((DeliveryDate>=DATEVALUE(Month1))*(DeliveryDate<=DATEVALUE(EndMonth1)))

Month1's formula is =EDATE(C2,1) referencing a cell with a formula displaying the first day of the current month. Basically I'm trying to set it up so I won't have to manually go through and change the months around, I'll just be able to drop in the info I need on a separate sheet and the main sheet will fill itself in. Am I referencing one too many cells here for my SUMPRODUCT formula? When I change it to this:

=SUMPRODUCT((DeliveryDate>=DATEVALUE("1/1/2018"))*(DeliveryDate<=DATEVALUE("1/31/2018")))

This works fine, but I figured using cell referencing I'd be able to forgo the entering in the date part. Any insight would be greatly appreciated.

 

[Joe4]

The EDATE formula returns a date. So you don't need to use the DATEVALUE function on it. That is just used to convert a Text entry to a Date one. Using it on a Date will return the #VALUE error.
So just try:

[COLOR=#333333]=SUMPRODUCT((DeliveryDate>=Month1)*(DeliveryDate<=EndMonth1))[/COLOR]

 

[shaun86]

Yes that worked perfectly, thanks! Was just overthinking it (or more accurately not understanding it).

 

[Joe4]

You are welcome!