# How to count by month and category with COUNTIFS in Excel

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

**Excel:** `=COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Ticket | Date | Category |  | Category | 2026-01-01 | 2026-02-01 | 2026-03-01 |
| T-1 | 2026-01-05 | Billing |  | Billing |  |  |  |
| T-2 | 2026-01-12 | Technical |  | Technical |  |  |  |
| T-3 | 2026-01-19 | Technical |  | Account |  |  |  |
| T-4 | 2026-01-30 | Account |  |  |  |  |  |
| T-5 | 2026-02-02 | Billing |  |  |  |  |  |
| T-6 | 2026-02-10 | Billing |  |  |  |  |  |
| T-7 | 2026-02-18 | Technical |  |  |  |  |  |
| T-8 | 2026-02-27 | Billing |  |  |  |  |  |
| T-9 | 2026-03-03 | Account |  |  |  |  |  |
| T-10 | 2026-03-11 | Technical |  |  |  |  |  |
| T-11 | 2026-03-20 | Billing |  |  |  |  |  |
| T-12 | 2026-03-31 | Technical |  |  |  |  |  |

Formula in F2, filled to H4: `=COUNTIFS($C$2:$C$13,$E2,$B$2:$B$13,">="&F$1,$B$2:$B$13,"<="&EOMONTH(F$1,0))`

Result: 1, 3, 1; 2, 1, 2; 1, 0, 1

## 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.

## Other ways

- `=SUMPRODUCT(($C$2:$C$13=$E2)*(TEXT($B$2:$B$13,"yyyy-mm")=TEXT(F$1,"yyyy-mm")))`: 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 `$E2` the category shifts right as you fill across, and without `F$1` the 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-by-month-and-category