Question: The formula I have is counting cells that are blank but I only need it to count the cells that contain a date. I've tried a few modifications to it but nothing seems to work. Can someone help me please?
=SUMPRODUCT(($I$2:$I$501>=DATEVALUE("07/01/2020"))*($I$2:$I$501<=DATEVALUE("07/31/2020")))
Thank you,
Could you provide the data source here? I tested this formula without problem:
As @emizhang-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 the DATE formula, which takes the year, month, and day as separate arguments.
So I would e.g. replace instances of DATEVALUE("07/01/2020") with DATE(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))
