# How to exclude several values from a count with COUNTIFS in Excel

Give COUNTIFS one "not equal" condition per value on the same range: `=COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2)`.

**Excel:** `=COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Ticket | Status |  |  | Exclude | Closed |
| T-201 | Open |  |  | and | Cancelled |
| T-202 | Closed |  |  | Active tickets |  |
| T-203 | In progress |  |  |  |  |
| T-204 | Cancelled |  |  |  |  |
| T-205 | Open |  |  |  |  |
| T-206 | Waiting |  |  |  |  |
| T-207 | Closed |  |  |  |  |
| T-208 | Open |  |  |  |  |
| T-209 | Cancelled |  |  |  |  |
| T-210 | In progress |  |  |  |  |

Formula in F3: `=COUNTIFS(B2:B11,"<>"&F1,B2:B11,"<>"&F2)`

Result: 6

## How it works

COUNTIFS joins its conditions with AND, so a ticket counts only if its status is not "Closed" **and** not "Cancelled". Repeating the same range with a second condition is allowed.

Ten tickets minus two closed and two cancelled leaves six.

## Other ways

- `=ROWS(B2:B11)-SUM(COUNTIF(B2:B11,F1:F2))`: All rows minus the ones to exclude. COUNTIF with a range of criteria returns one count per value, so the list can be as long as you like.
- `=SUMPRODUCT(--ISNA(MATCH(B2:B11,F1:F2,0)))`: Counts statuses that MATCH can't find in the exclude list. Best when the list is long and kept in cells.

## Common mistakes

- Using COUNTIF with an array, like `=COUNTIF(B2:B11,{"<>Closed","<>Cancelled"})`, returns two counts that each include the other status. Summing them double counts.
- Blank statuses are counted as "not Closed". Add `,B2:B11,"<>"` to skip them.

## Questions

**How do I exclude values and also require one?**

Mix the conditions: `=COUNTIFS(B2:B11,"<>Closed",B2:B11,"<>Cancelled",C2:C11,"High")`.

**Can the excluded values contain wildcards?**

Yes: `"<>*test*"` excludes every status that contains "test".

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/countifs-exclude-multiple-values