# How to count cells that match any value in a list in Excel

Ask COUNTIF whether each cell appears in the list: `=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0))` counts the rows whose value is on the list.

**Excel:** `=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>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 |
| --- | --- | --- | --- | --- | --- | --- |
| Ticket | Customer |  |  | Key accounts |  | Tickets |
| K-01 | Contoso |  |  | Contoso |  |  |
| K-02 | Fabrikam |  |  | Litware |  |  |
| K-03 | Litware |  |  | Adatum |  |  |
| K-04 | Northwind |  |  |  |  |  |
| K-05 | Contoso |  |  |  |  |  |
| K-06 | Adatum |  |  |  |  |  |
| K-07 | Proseware |  |  |  |  |  |
| K-08 | Contoso |  |  |  |  |  |
| K-09 | Wingtip |  |  |  |  |  |
| K-10 | Litware |  |  |  |  |  |
| K-11 | Fabrikam |  |  |  |  |  |
| K-12 | Tailspin |  |  |  |  |  |

Formula in G2: `=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0))`

Result: 6

## How it works

Here COUNTIF is turned around: the range is the list and the criteria is the customer column, so it returns, for each ticket, how many times that customer appears on the list. `>0` turns that into TRUE or FALSE, and SUMPRODUCT counts the TRUEs.

Contoso has three tickets, Litware two and Adatum one, so six tickets come from key accounts. Extend the list and the count follows.

## Other ways

- `=SUMPRODUCT(--ISNUMBER(MATCH(B2:B13,E2:E4,0)))`: MATCH finds each customer in the list, and ISNUMBER turns found into TRUE. Same result.
- `=SUMPRODUCT(COUNTIF(B2:B13,E2:E4))`: Counts the tickets for each list entry and adds them. Same result unless the list has a value twice, which would be counted twice.

## Common mistakes

- If the list contains a blank cell, COUNTIF and MATCH treat it as a criteria for blank, which can count empty rows. Keep the list free of blanks or use a range that ends at the last entry.
- Extra spaces in either list stop a match. TRIM both lists if they come from different systems.

## Questions

**How do I count cells that are not in the list?**

Flip the test: `=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)=0))`.

**How do I add another condition, like open tickets only?**

Multiply it in: `=SUMPRODUCT((COUNTIF(E2:E4,B2:B13)>0)*(C2:C13="Open"))`.

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-cells-that-match-a-list