Function Problems

How to Prevent Blank Cells from Being Counted as January Dates

Chanuka GeekiyanageChanuka Geekiyanage Nov 10, 2026 870 views

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.

How to Prevent Blank Cells from Being Counted as January Dates
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 you start

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.

Solution 1Recommended

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.

1
Select your formula cell

Double-click the cell containing your current SUMPRODUCT formula to edit it.

2
Add the ISNUMBER condition

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

3
Complete the SUMPRODUCT formula

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.

Use the ISNUMBER Function to Exclude Blanks
Formula Tip: You can change the '=1' part of the formula to any number from 1 to 12 to accurately count dates falling in different months.
Advanced Data Analysis

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. 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the Excel file containing your monthly calculations.
  2. 2. Use the Formula tab: Navigate to the Formulas tab to easily access the Date & Time and Math & Trig function libraries for reference.
  3. 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.
100% compatible with Microsoft Excel formulas and functions (.xlsx format)Intelligent formula auto-completion and syntax highlightingBuilt-in Date & Time functions for advanced data analysisLightweight, fast, and completely free to use
microsoft office alternative - wps office

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.