How to Prevent Blank Cells from Being Counted as January Dates
Question details
The user needs a way to prevent empty or zero-value cells from being incorrectly evaluated as January when using the MONTH function inside a SUMPRODUCT formula.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Using the MONTH() function within a SUMPRODUCT() formula to count or sum data based on specific months.
- Observed behavior
- Blank cells or cells containing the number 0 are counted as month 1 (January) because the formula MONTH(0) evaluates to 1, causing inaccurate data summaries.
Before adjusting your formula, verify that the cells you intend to evaluate are correctly formatted as 'Date' and identify the exact ranges containing your date values and potential blank cells.
Use the ISNUMBER Function to Exclude Blanks
Filter out blank cells and text strings by nesting an ISNUMBER check inside your SUMPRODUCT formula.
In spreadsheet programs, date values are stored as serial numbers starting from January 1, 1900. Because a blank cell evaluates to a numerical 0, the formula MONTH(0) interprets this as the first day of the 1900 calendar, returning 1 (January).
By using the ISNUMBER function, you can force the SUMPRODUCT formula to only calculate rows that contain valid numerical date values, ignoring blank cells completely.
Double-click the cell containing your current SUMPRODUCT formula to edit it.
Multiply your MONTH condition by an ISNUMBER condition. For example, if your dates are in the range E2:E32, use `(ISNUMBER(E2:E32))*(MONTH(E2:E32)=1)`.
If you are checking multiple columns (e.g., columns E and H), structure the complete formula like this: `=SUMPRODUCT((ISNUMBER(E2:E32))*(MONTH(E2:E32)=1)+(ISNUMBER(H2:H32))*(MONTH(H2:H32)=1))` and press Enter.

Exclude Blanks Explicitly Using the Not Equal Operator
Exclude blank cells from the calculation by adding a mathematical condition that checks if the cell is not empty.
Calculate Dates Accurately with WPS Spreadsheet
Handling complex array formulas like SUMPRODUCT and MONTH is effortless in WPS Spreadsheet. It provides a robust formula engine, intelligent error checking, and a familiar interface to streamline your data analysis workflows.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the Excel file containing your monthly calculations.
- 2. Use the Formula tab: Navigate to the Formulas tab to easily access the Date & Time and Math & Trig function libraries for reference.
- 3. Apply the corrected formula: Enter your ISNUMBER and SUMPRODUCT combination. WPS Spreadsheet will automatically calculate the correct month count while ignoring blank cells seamlessly.

Frequently Asked Questions
Why does Excel think a blank cell is January?
In spreadsheet programs like Excel and WPS Spreadsheet, dates are stored as serial numbers starting from January 1, 1900. A blank cell evaluates to a serial number of 0. When you pass 0 into the MONTH() function, the system interprets it as the first month of 1900, returning 1 (January).
Can I use COUNTIFS instead of SUMPRODUCT to count dates by month?
Yes, you can use COUNTIFS, which often handles blank cells more gracefully without extra conditions. However, COUNTIFS requires you to specify the exact start and end date of the month (e.g., `>=01/01/2023` and `<=01/31/2023`) rather than just evaluating the month number directly like SUMPRODUCT does.
Will the ISNUMBER function ignore cells containing text?
Yes. The ISNUMBER function only returns TRUE for cells containing numbers. Because dates are mathematically stored as numbers, real dates will return TRUE. It will return FALSE for blanks, errors, and text strings, preventing them from being falsely counted as January in your formula.




