# How to filter by a list of values in Excel

Use COUNTIF as the condition: `=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11))` keeps every row whose customer appears in the list in `G2:G4`.

**Excel:** `=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11))` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Order | Date | Customer | Region | Amount |  | Customers |
| SO-1041 | 2026-02-23 | Northwind | West | 1840 |  | Contoso |
| SO-1042 | 2026-03-02 | Contoso | East | 420 |  | Litware |
| SO-1043 | 2026-03-09 | Fabrikam | West | 395 |  | Wingtip |
| SO-1044 | 2026-03-15 | Litware | North | 1720 |  |  |
| SO-1045 | 2026-03-21 | Adatum | South | 2210 |  |  |
| SO-1046 | 2026-03-31 | Proseware | East | 1955 |  |  |
| SO-1047 | 2026-04-06 | Wingtip | West | 610 |  |  |
| SO-1048 | 2026-04-14 | Tailspin | South | 380 |  |  |
| SO-1049 | 2026-04-20 | Lucerne | North | 1100 |  |  |
| SO-1050 | 2026-04-28 | Contoso | East | 865 |  |  |

Formula in I2: `=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11))`

Result (spills 4 × 5): SO-1042, 3/2/2026, Contoso, East, $420; SO-1044, 3/15/2026, Litware, North, $1,720; SO-1047, 4/6/2026, Wingtip, West, $610; SO-1050, 4/28/2026, Contoso, East, $865

## How it works

With a whole column as its second argument, COUNTIF runs once per order: for each customer in `C2:C11` it counts how many times that name appears in the list. The result is 1 for listed customers and 0 for the rest, and FILTER keeps the rows that aren't 0.

Edit the list and the result follows, so the list works like a set of checkboxes for the report.

## Other ways

- `=FILTER(A2:E11,ISNUMBER(MATCH(C2:C11,G2:G4,0)))`: MATCH returns a position for listed customers and #N/A for the rest. Use it if the names can contain * or ?, which COUNTIF reads as wildcards.
- `=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11)=0)`: Add `=0` to do the opposite and leave the listed customers out. (Every order except the ones for listed customers.)

## Common mistakes

- The ranges go in this order: the list first, then the column to check. `COUNTIF(C2:C11,G2:G4)` returns three counts, not one per order, and FILTER fails.
- A trailing space in the list ("Contoso ") stops it matching. TRIM the list if it was pasted from somewhere else.
- Make the list range taller than you need, such as `G2:G20`. Blank cells in it do no harm, and new names are picked up.

## Questions

**How do I exclude a list of values instead?**

Test for a count of 0: `=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11)=0)`.

**Does this work in Google Sheets?**

Yes, the same formula works. FILTER evaluates the COUNTIF once per row without ARRAYFORMULA.

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/filter-by-a-list-of-values