# Excel SUMPRODUCT Formula Question

6 Reputation points
2020-07-29T12:50:25.473+00:00

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,

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,693 questions
{count} vote

1. 1 Reputation point
2020-09-04T19:37:36.27+00:00

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:

1. 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)`
2. 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))`