As @Emi Zhang-MSFT mentioned, the formula provided appears to be working, but perhaps you have some added complexity you couldn't share that causes it to include blank cells.
If that is the case, then in general, you can use COUNTBLANK
to get a count of blank cells, and subtract that from the final result. For example:
=SUMPRODUCT(($I$2:$I$501>=DATEVALUE("07/01/2020"))*($I$2:$I$501<=DATEVALUE("07/31/2020"))+($I$2:$I$501=""))-COUNTBLANK($I$2:$I$501)
Here I've added a sample condition to your SUMPRODUCT
that would cause blank cells to get included in the total, but this is negated by the final addition of COUNTBLANK
.
A couple other comments if I may:
- You should avoid using the
DATEVALUE
formula unless attempting to parse user input, as this will only work in the US or other locales that share your own "mm/dd/yyyy" date format. Something that would work universally would be theDATE
formula, which takes the year, month, and day as separate arguments.
So I would e.g. replace instances ofDATEVALUE("07/01/2020")
withDATE(2020,7,1)
- Your formulas might be simpler and easier to debug if you used the
COUNTIFS
formula. This formula has been available since Excel 2007.
Here's what your formula would look like with both of the above improvements:
=COUNTIFS($I$2:$I$501,">="&DATE(2020,7,1),$I$2:$I$501,"<"&DATE(2020,8,1))