How to Count Dates by Month in Excel

To count by month in Excel, give COUNTIFS a date range: every date on or after the first day of the month, and on or before the last day. EOMONTH works out the last day for you, so one formula fills down a whole monthly summary. This guide shows that method, how to add more conditions, and when SUMPRODUCT or a PivotTable is the better choice.

How to count dates in one month with COUNTIFS#

Count the dates between the first and last day of the month. COUNTIFS counts rows that meet every condition you give it, and you can test the same column twice. Here's the order list the examples use:

To count orders in March 2025:

=COUNTIFS(B2:B8,">="&DATE(2025,3,1),B2:B8,"<="&DATE(2025,3,31))

The result is 4: orders 1002, 1003, 1004 and 1007. Order 1006 is in March too, but in 2024, so it's left out. A date range always checks the year as well as the month.

Notice the &. The operator goes in quotes and is joined to the date. Writing ">=DATE(2025,3,1)" wouldn't work, because inside quotes the formula is just text. The COUNTIFS function guide covers the syntax, and the comparison of COUNTIF and COUNTIFS shows when you need the multi-condition version.

How to use a month cell and EOMONTH#

Put the first day of the month in a cell and let EOMONTH find the last day. EOMONTH returns the last day of a month; with 0 as its second argument, it's the same month as the date you give it:

=COUNTIFS(B2:B8,">="&E2,B2:B8,"<="&EOMONTH(E2,0))

If E2 holds 1 March 2025, EOMONTH(E2,0) is 31 March 2025, and the result is again 4. You never have to remember whether a month has 30 or 31 days, or whether February has 29. The EOMONTH function guide has more examples.

To build a monthly summary, list month start dates down column E and fill the formula down with locked ranges:

The formula in F2 is =COUNTIFS($B$2:$B$8,">="&E2,$B$2:$B$8,"<="&EOMONTH(E2,0)). The month cells hold real dates (1 February, 1 March, 1 April) with a custom format of mmm yyyy. To generate the list, type the first month in E2 and put =EDATE(E2,1) in E3, then fill down.

How to count by month with another condition#

Add another range and criteria pair. To count only East orders in March 2025:

=COUNTIFS(B2:B8,">="&E2,B2:B8,"<="&EOMONTH(E2,0),C2:C8,"East")

The result is 2: orders 1003 and 1004. You can keep adding pairs, such as a product or a status, and COUNTIFS counts only the rows that match all of them. The guide to COUNTIFS and SUMIFS with multiple conditions has more examples.

To add up amounts by month instead of counting rows, use SUMIFS with the same date criteria. That's covered in how to sum by month.

What if the dates include times?#

If your dates include times, use "less than the first day of the next month" for the end. A timestamp like 31/03/2025 14:30 is stored as a number slightly larger than 31/03/2025, so "<="&EOMONTH(E2,0) misses it.

This version counts the whole last day:

=COUNTIFS(B2:B8,">="&E2,B2:B8,"<"&EOMONTH(E2,0)+1)

EOMONTH(E2,0)+1 is 1 April, and "less than 1 April" includes every moment of 31 March. It works for plain dates too, so it's a safe habit for system exports and logs.

How to count by month number with SUMPRODUCT#

SUMPRODUCT can test MONTH directly, without start and end dates. To count every March date:

=SUMPRODUCT(--(MONTH(B2:B8)=3))

MONTH returns the month number of each date, the comparison gives TRUE or FALSE, and -- turns those into 1s and 0s for SUMPRODUCT to add up. On the order list, the result is 5, not 4, because MONTH ignores the year and includes March 2024.

Add a YEAR test to fix that:

=SUMPRODUCT((MONTH(B2:B8)=3)*(YEAR(B2:B8)=2025))

This returns 4. Watch out for blank cells: MONTH of an empty cell is 1, so empty rows count as January. That's one more reason COUNTIFS with a date range is usually the safer choice.

How to count by month with a PivotTable#

A PivotTable builds a monthly count without formulas. Select the data, go to Insert > PivotTable, and drag Date to Rows and Order to Values. Set the value to Count.

Newer Excel versions group dates by year, quarter and month automatically. If not, right-click a date in the PivotTable, choose Group, and select Months and Years. Always include years when your data covers more than one, or March 2024 and March 2025 end up in the same row.

Common mistakes when counting by month#

Most wrong counts come from criteria syntax, years or text dates. Check these:

1. Operator and date not joined. Use ">="&E2, not ">=E2".
2. Month cell isn't the 1st. If E2 holds 15 March, the count starts mid-month. Use =DATE(YEAR(A2),MONTH(A2),1) to get the 1st.
3. Years mixed together. MONTH-based formulas combine years. Use date ranges or add a YEAR test.
4. Text dates. Dates stored as text are never counted. Convert them to real dates.
5. Ranges of different sizes. Every range in COUNTIFS must cover the same rows, or you get #VALUE!.

For filtering the matching rows rather than counting them, see how to filter by date, and for any custom period, summing between two dates.

Practise it#

Monthly counts and totals are among the most common tasks in data and reporting job tests. Practise using dates in criteria in a live spreadsheet, then practise COUNTIFS and SUMIFS with several conditions. Each formula is checked instantly. Or try the exercise on this page first.