Function Problems

Count Dates in a Specific Month in an Excel Table

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Count dates belonging to a specific month in an Excel table.

Count Dates in a Specific Month in an Excel Table
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to count October dates directly from a table's date column.
Observed behavior
A formula should evaluate each date's month and return the number of October entries.
Before you start

Check the table name and date column header in Excel, and confirm that the entries are stored as dates rather than text. Decide whether to count the month across all years or only within a specific year.

Solution 1Recommended

Count a Month Across All Years with MONTH and SUM

Use an array formula to count October dates in an Excel table, regardless of year.

1
Select the result cell

Click an empty cell where you want the count to appear.

2
Enter the formula

Type =SUM(--(MONTH(Table1[Date])=10)), replacing Table1 and Date with your table name and date column header. Change 10 to the number of the month you want to count.

3
Confirm the formula

Press Enter in Microsoft 365. In older Excel versions, the formula may require array entry; on Windows, press Ctrl+Shift+Enter.

Count a Month Across All Years with MONTH and SUM
Check the source cells: The formula counts matching months across all years. Invalid text or error values in the date column can cause an error; blank cells can also affect a January count.
Count dates in WPS Spreadsheet

Count Dates by Month in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use SUMPRODUCT with MONTH to count October dates across a selected range.

  1. 1. Open the workbook: Open WPS Spreadsheet, choose Open, and select your Excel workbook.
  2. 2. Identify the date range: Find the cells containing your dates, such as A2:A100, and check that they contain valid dates.
  3. 3. Enter the formula: Click an empty cell and enter =SUMPRODUCT(--(MONTH(A2:A100)=10)), replacing A2:A100 with your date range.
  4. 4. View the count: Press Enter to see the number of October dates. Change 10 to another month number as needed.
Count a selected month across multiple yearsOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and controlsWork with spreadsheets using a free office suite
QA img-9

Frequently Asked Questions

How do I count a different month?

Change the number after the equals sign in the MONTH comparison. For example, use =SUM(--(MONTH(Table1[Date])=3)) to count March dates.

Does the MONTH formula count dates from every year?

Yes. It compares the month number only. To count a particular year, use COUNTIFS with a start date and an exclusive start date for the following month.

Why does the formula return an error?

Check Table1[Date] for invalid text or error values and verify that the table and column names match your workbook. MONTH needs valid date values.

Why might blank cells affect a January count?

MONTH can interpret blank cells as zero, which may be treated as January. Remove blanks from the referenced range or use date boundaries for a specific January with COUNTIFS.