How to count by month and category with COUNTIFS in Excel
Formula (Excel and Google Sheets)
=COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0))Build a grid with categories down and month starts across, and use mixed references: =COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0)).
- Result on the example
- one result per row in
F2, filled down toH4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to H4. Change any input and the result updates, or edit the formula to experiment.
How it works
Three conditions: the category equals the label in column E, and the date falls from the month start in row 1 to that month's last day. $E2 locks the column, so every cell in a row reads its category; F$1 locks the row, so every cell in a column reads its month. The data ranges are fully locked.
That one formula fills the whole grid: Billing had 3 tickets in February, Account none.
=COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0))Result1
$C$2:$C$1312 rows × 1 col: "Billing", "Technical", "Technical", "Account", …A range: 12 cells in one column.
$E2"Billing"The value in E2.
$B$2:$B$1312 rows × 1 col: 1/5/2026, 1/12/2026, 1/19/2026, 1/30/2026, …A range: 12 cells in one column.
F$1Jan 2026The value in F1.
">="&F$1">=46023"The & operator joins as text.
EOMONTH(F$1,0)1/31/2026 (46053)EOMONTH: Last day of the month, months away.- start_date
F$1Jan 2026 - months
0
- start_date
"<="&EOMONTH(F$1,0)"<=46053"The & operator joins as text.
COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0))1COUNTIFS: Counts rows that meet every condition.- criteria_range1
$C$2:$C$1312 rows × 1 col: "Billing", "Technical", "Technical", "Account", … - criteria1
$E2"Billing" - criteria_range2
$B$2:$B$1312 rows × 1 col: 1/5/2026, 1/12/2026, 1/19/2026, 1/30/2026, … - criteria2
">="&F$1">=46023" - criteria_range3
$B$2:$B$1312 rows × 1 col: 1/5/2026, 1/12/2026, 1/19/2026, 1/30/2026, … - criteria3
"<="&EOMONTH(F$1,0)"<=46053"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(($C$2:$C$13=$E2)*(TEXT($B$2:$B$13,"yyyy-mm")=TEXT(F$1,"yyyy-mm"))) | 1 3 1, 2 1 2, 1 0 1 | Matches a year-month label instead of a date range; works when row 1 holds labels such as 2026-01. |
Common mistakes
- Getting the $ signs wrong is the usual failure: without
$E2the category shifts right as you fill across, and withoutF$1the month shifts down. - Row 1 must hold real dates (first of each month), formatted to show the month. Text like "Jan" can't be compared with dates.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count by month and category with COUNTIFS
F2, count the tickets in the category in column E and the month in row 1, then fill the formula across and down to H4.Questions
How do I add a total row and column?
Use SUM on each row and column of the grid, or COUNTIF on the category alone for row totals.
Is a pivot table easier?
For a one-off, yes: Category in Rows, Date in Columns grouped by month, and Ticket in Values as Count. The formula grid stays live without refreshing.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.