Function Problems

How to Count November Dates with SUMPRODUCT in Excel

Ayan Masood Oct 9, 2026 869 views

Question details

The user needs to count specific rows in a dataset where one column matches a text string and another date column falls specifically in November.

How to Count November Dates with SUMPRODUCT in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering and counting data based on multiple conditions, specifically requiring the extraction of a month from a date field while checking for a text status.
Observed behavior: The user is seeking the correct formula syntax to combine SUMPRODUCT and MONTH functions to accurately evaluate both conditions simultaneously.

Before you start

Ensure that the dates in your target column are formatted as valid dates rather than plain text, otherwise the MONTH function will return an error.

Solution 1Recommended

Use SUMPRODUCT combined with the MONTH function

You can use SUMPRODUCT to evaluate multiple arrays and conditions simultaneously, extracting the month directly within the formula.

The SUMPRODUCT function handles arrays natively, which means you do not need to use Ctrl+Shift+Enter. By embedding the MONTH function, you can extract the month number (11 for November) from your date range and multiply it by your text condition.

Select the destination cell

Click on an empty cell where you want the final count result to be displayed.

Enter the SUMPRODUCT formula

Type the formula =SUMPRODUCT((MONTH(B2:B4)=11)*(E2:E4="NEW")) into the formula bar.

Adjust cell ranges

Change the ranges B2:B4 and E2:E4 to match the actual rows containing your dates and text statuses.

Calculate the result

Press the Enter key to execute the formula and view the total count of rows meeting both criteria.

Use SUMPRODUCT combined with the MONTH function

Matching Array Sizes: Ensure that both ranges specified in the formula (e.g., B2:B4 and E2:E4) are exactly the same size. If the ranges differ in length, the SUMPRODUCT function will return a #VALUE! error.

WPS Spreadsheet Solution

Easily Count Multi-Condition Data in WPS Spreadsheet

WPS Spreadsheet natively supports advanced array-like functions such as SUMPRODUCT and MONTH, allowing you to use the exact same formulas to analyze your data efficiently without any syntax changes.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your dates and text data.
2. Start the formula: Select your target cell and type =SUMPRODUCT( to trigger the formula hint.
3. Input the conditions: Enter your specific conditions, such as (MONTH(B2:B100)=11)*(E2:E100="NEW").
4. Apply and count: Add the closing parenthesis and press Enter to instantly calculate your results.

100% compatibility with Microsoft Excel formulas and .xlsx filesFree, lightweight, and fast spreadsheet softwareBuilt-in formula syntax checking and auto-completionSupports complex multi-condition data analysis out of the box

Frequently Asked Questions

Can I use COUNTIFS instead of SUMPRODUCT to count November dates?

Yes, but COUNTIFS requires a specific date range rather than a MONTH extraction. You would need to use criteria like ">=11/1/2023" and "<=11/30/2023". SUMPRODUCT is often preferred when you want to count a specific month across multiple different years without specifying start and end dates.

Why is my SUMPRODUCT formula returning a #VALUE! error?

This error typically occurs if the ranges in your formula are not the same size (for example, B2:B10 and E2:E11), or if there is text data inside the date column that the MONTH function is unable to process into a valid month number.

How do I count a different month using this formula?

Simply change the number 11 in the MONTH(B2:B4)=11 segment of the formula to the numeric equivalent of your target month (e.g., use 1 for January, or 12 for December).

Will this formula count November dates across different years?

Yes, the MONTH function ignores the year and only extracts the month value. If you only want to count November dates for a specific year, you must add a third condition to your SUMPRODUCT formula, such as *(YEAR(B2:B4)=2023).

Excel & Data Problems