How to count cells that match any value in a list in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0))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.
- Result on the example
- 6 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0))Result6
E2:E43 rows × 1 col: "Contoso", "Litware", "Adatum"A range: 3 cells in one column.
B2:B1312 rows × 1 col: "Contoso", "Fabrikam", "Litware", "Northwind", …A range: 12 cells in one column.
COUNTIF(E2:E4,B2:B13){12 rows × 1 col} 1, 0, 1, 0, …COUNTIF: Counts cells that meet one condition.- range
E2:E43 rows × 1 col: "Contoso", "Litware", "Adatum" - criteria
B2:B1312 rows × 1 col: "Contoso", "Fabrikam", "Litware", "Northwind", …
- range
COUNTIF(E2:E4,B2:B13)>0{12 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The > operator tests greater than.
-(COUNTIF(E2:E4,B2:B13)>0{12 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--(COUNTIF(E2:E4,B2:B13)>0{12 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0))6SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(COUNTIF(E2:E4,B2:B13)>0{12 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(COUNTIF(E2:E4,B2:B13)>0)). We ran it in Google Sheets mode on the same data and it returns 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--ISNUMBER(MATCH(B2:B13,E2:E4,0))) | 6 | MATCH finds each customer in the list, and ISNUMBER turns found into TRUE. Same result. |
| =SUMPRODUCT(COUNTIF(B2:B13,E2:E4)) | 6 | 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.
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 cells that match any value in a list
G2, count the tickets from customers on the key-account list in E2:E4.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")).
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.