How to Count by Category in Excel

To count by category in Excel, write each category once and put a COUNTIF formula next to it: =COUNTIF($B$2:$B$9,F2) returns how many rows belong to the category in F2. This guide is for anyone who needs a quick "how many of each?" table for a report or a job test. It covers the formula, an automatic category list with UNIQUE, a second condition, percentages and PivotTables.

The sample data#

All the examples use this small order list in A1:D9. Each order has one category:

The question is: how many orders are in each category? The answer should be Furniture 2, Office 3 and Tech 3.

How to count one category with COUNTIF#

COUNTIF counts the cells in a range that match one condition, so it can count one category at a time. The syntax is =COUNTIF(range, criteria):

=COUNTIF(B2:B9,"Office")

The result is 3 (orders 1002, 1004 and 1007). The range is the Category column, and the criteria is the category name in double quotes. COUNTIF ignores capitals, so "office" gives the same answer. The COUNTIF function guide lists every type of criteria it accepts.

How to build a count by category table#

A count by category table has one row per category and one COUNTIF per row. Point the criteria at a cell instead of typing the name, so one formula works for every row.

1. In F1 type Category and in G1 type Count.
2. In F2:F4 type Furniture, Office and Tech.
3. In G2 type the formula below and press Enter.
4. Select G2:G4 and fill down (Windows: Ctrl+D; Mac: ⌘D).

=COUNTIF($B$2:$B$9,F2)

The $ signs lock the range, so it stays on rows 2 to 9 when you fill down. F2 has no $, so it becomes F3 and F4. To add the $ signs quickly, click inside the range and press F4 on Windows or ⌘T on a Mac.

The same layout works for totals. Swap COUNTIF for SUMIF and you get the total for each category instead of the count.

Build the category list automatically with UNIQUE#

UNIQUE returns each value in a range once, so you don't have to type the categories yourself. It needs Excel 2021 or Microsoft 365. In F2, type:

=UNIQUE(B2:B9)

The result spills (fills the cells below on its own) into F2:F4: Furniture, Office, Tech, in the order they first appear. Wrap it in SORT for A to Z order: =SORT(UNIQUE(B2:B9)). The UNIQUE function guide explains the optional arguments.

Then count every category in one formula. In G2, type:

=COUNTIF(B2:B9,F2#)

The # after F2 means "the whole spilled list". When a new category appears in column B, both lists grow by themselves. If you only want to know how many different categories there are, see how to count unique values.

How to count by category with a second condition#

Use COUNTIFS when each row must match the category and something else. COUNTIFS takes pairs of ranges and criteria, and a row is counted only when every pair matches. To count Tech orders in the North region:

=COUNTIFS(B2:B9,"Tech",C2:C9,"North")

The result is 2 (orders 1003 and 1008). Conditions on numbers work too. This counts Tech orders of 800 or more:

=COUNTIFS(B2:B9,"Tech",D2:D9,">=800")

The result is 2 (1,200 and 950). For a full grid, put categories down column F and regions across row 1, then use =COUNTIFS($B$2:$B$9,$F2,$C$2:$C$9,G$1) and fill it right and down. The mixed $ signs keep the category column and the region row fixed.

Show each category as a percentage#

To see each category's share, divide its count by the total number of rows. COUNTA counts all filled cells, so it gives the total:

=COUNTIF($B$2:$B$9,F2)/COUNTA($B$2:$B$9)

For Office, that is 3 / 8 = 0.375. Format the cell as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%), then add one decimal place to show 37.5%. Furniture is 25% and Tech is 37.5%, which adds up to 100%.

How to count by category with a PivotTable#

A PivotTable counts by category without any formulas, which is handy for large lists.

1. Click any cell in the data.
2. Go to Insert > PivotTable and click OK to place it on a new sheet.
3. Drag Category to the Rows area.
4. Drag Order (or Category again) to the Values area.

If the field in Values holds text, Excel shows Count by default. If it holds numbers, like Order here, Excel shows Sum. Click the field in Values, choose Value Field Settings and pick Count. The PivotTable doesn't update on its own: right-click it and choose Refresh after the data changes.

Formulas update instantly and are what most job tests ask for. Practise the formula version with the pivot-style summary lesson.

Why your category counts are wrong#

If the counts don't add up to the number of rows, the category column usually has messy values. Check the total first: =SUM(G2:G4) should equal =COUNTA(B2:B9), which is 8 here.

• Extra spaces. "Tech " with a trailing space doesn't match "Tech". Clean the column with TRIM.
• Different spellings. "Furniture" and "Furnitures" are two categories to Excel. UNIQUE shows them both, which makes them easy to spot.
• Blank cells. Rows with no category aren't counted by any COUNTIF. Count them with =COUNTBLANK(B2:B9).
• Missing categories. A typed list can miss a category. UNIQUE never does.

Counting duplicates is a close cousin of this task; see how to count duplicates. If your categories are simple answers like Yes and No, the guide on counting Yes in Excel covers that case.

Practise it#

Try the exercise on this page first: it counts one category from a cell. Then practise COUNTIF in a live spreadsheet, where you type the formula and it checks it instantly, and move on to the pivot-style summary lesson to build a full count and total table by category.