# How to count comma-separated items in a cell in Excel

Count the commas and add 1, returning 0 for an empty cell: `=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)`.

**Excel:** `=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)` (Excel 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Order | Items | Item count |
| O-501 | Desk, Chair, Lamp |  |
| O-502 | Monitor |  |
| O-503 |  |  |
| O-504 | Keyboard, Mouse |  |
| O-505 | Desk, Chair, Lamp, Monitor arm, Cable tray |  |
| O-506 | Webcam, Headset |  |
| O-507 | Chair |  |

Formula in C2, filled to C8: `=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)`

Result: 3; 1; 0; 2; 5; 2; 1

## How it works

A list of n items has n-1 commas. Removing the commas with SUBSTITUTE and comparing lengths gives the number of commas, and adding 1 gives the items. The IF handles the empty order, which has no commas but also no items.

## Other ways

- `=IF(TRIM(B2)="",0,COUNTA(TEXTSPLIT(B2,",")))`: Excel 2024 and Microsoft 365: TEXTSPLIT splits the list into cells and COUNTA counts them.
- `=IF(TRIM(B2)="",0,COUNTA(SPLIT(B2,",")))`: Google Sheets: SPLIT does the same job, and it skips empty items from doubled commas.

## Common mistakes

- A trailing comma ("Desk, Chair,") counts an extra, empty item with the comma method. Clean the list, or use the Sheets SPLIT version, which ignores empty parts.
- Without the IF, an empty cell returns 1.

## Questions

**How do I count items separated by semicolons or line breaks?**

Change the separator: `";"`, or `CHAR(10)` for line breaks.

**How do I count how many orders include a particular item?**

Use COUNTIF with wildcards: `=COUNTIF(B2:B8,"*Chair*")`.

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-items-in-a-comma-separated-cell