Formula Errors

Fix SUMPRODUCT Counting Blank Dates as January in Excel

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to accurately count the number of dates falling in January using the SUMPRODUCT and MONTH functions, but the formula is improperly including blank cells in the total count.

Product: Excel
Device & OS: not provided
Scenario: Counting the occurrences of dates that belong to January within a specific cell range using an array formula.
Observed behavior: Excel evaluates blank cells as zero (which represents the serial date January 0, 1900), causing the MONTH function to return 1. This inflates the total count of January dates.

Before you start

Verify that your selected date range consists only of valid dates or true blank cells, as cells containing invisible spaces or hidden characters will not trigger the empty cell exclusion logic.

Solution 1Recommended

Exclude Blank Cells Using Array Multiplication

Modify your existing SUMPRODUCT formula by adding a logical condition that checks if the cell is not empty before counting it.

To prevent Excel from evaluating empty cells as January 1900, you must explicitly tell the formula to ignore them. By multiplying your original MONTH array by an array that checks for non-blank cells, the blank cells evaluate to FALSE (0) and are removed from the sum.

Select the destination cell

Click on the cell where you want the final January count to be displayed.

Enter the modified formula

Type or paste the following formula: =SUMPRODUCT(--(MONTH(List!E3:E1502)=1)*(List!E3:E1502<>""))

Apply the calculation

Press the Enter key. The calculation will now return the correct count of January dates while completely ignoring the blank cells.

How the exclusion works: The expression (List!E3:E1502<>"") generates an array of TRUE and FALSE values. When multiplied, FALSE becomes 0, ensuring that blank cells contribute zero to your final SUMPRODUCT count.

Solution 2

Use the SUM Function in Newer Excel Versions

If you are using Microsoft 365, Excel 2021, or newer versions, you can leverage native dynamic arrays with the standard SUM function.

Calculate Complex Array Formulas Easily with WPS Office

WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT and dynamic array calculations. You can seamlessly process large datasets, fix blank date calculation errors, and perform sophisticated data analysis completely for free.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your date lists.
2. Select the target cell: Click on the cell where you want to output the correct January counts.
3. Enter the formula: Type =SUMPRODUCT(--(MONTH(List!E3:E1502)=1)*(List!E3:E1502<>"")) directly into the formula bar.
4. Calculate the result: Press Enter to instantly process the data and ignore the blank dates seamlessly.

100% compatible with Microsoft Excel formulas and .xlsx file formats.Natively handles complex array calculations like SUMPRODUCT and MONTH effortlessly.Lightweight, fast, and features a familiar user interface with no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel treat blank cells as January?

Excel stores dates as sequential serial numbers starting from January 1, 1900, which is represented by the number 1. An empty cell evaluates to zero, representing a hypothetical January 0, 1900. When you wrap a zero inside the MONTH function, it naturally returns 1 (January).

Can I use COUNTIFS instead of SUMPRODUCT for this calculation?

Yes, COUNTIFS is often a more robust alternative. You can count dates by specifying a start and end date criteria, such as =COUNTIFS(List!E3:E1502, ">=1/1/2023", List!E3:E1502, "<=1/31/2023"). This method automatically ignores blank cells without needing array multiplication.

How do I count a different month using this formula?

To count occurrences for another month, simply change the '=1' part of your MONTH formula to the corresponding month number. For instance, use '=2' for February, '=3' for March, or '=12' for December.

Excel & Data Problems