# How to sum values that are not in a list in Excel

Count each row's value in the list and add the rows where the count is 0: `=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10)`.

**Excel:** `=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Vendor | Amount |  | Exclude |  | Total of others |
| Delta Air | 1240 |  | Delta Air |  |  |
| Office Hub | 186 |  | Marriott |  |  |
| Marriott | 845 |  |  |  |  |
| Cloudly | 299 |  |  |  |  |
| Uber | 64 |  |  |  |  |
| Office Hub | 92 |  |  |  |  |
| Marriott | 610 |  |  |  |  |
| Printly | 148 |  |  |  |  |
| Cloudly | 299 |  |  |  |  |

Formula in F2: `=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10)`

Result: $1,088

## How it works

`COUNTIF(D2:D3,A2:A10)` checks each vendor against the list and returns how many times it appears there: 1 for Delta Air and Marriott, 0 for the rest. `=0` keeps the vendors that are not on the list, and SUMPRODUCT adds their amounts. Add names to the list and extend `D2:D3`.

## Other ways

- `=SUMIFS(B2:B10,A2:A10,"<>"&D2,A2:A10,"<>"&D3)`: One "not equal" condition per excluded value. Fine for two or three values.
- `=SUM(FILTER(B2:B10,COUNTIF(D2:D3,A2:A10)=0))`: Excel 365 and Google Sheets: FILTER keeps the vendors that aren't on the list.

## Common mistakes

- Several "<>" conditions must all be in one SUMIFS. Adding separate SUMIFS together counts most rows twice.
- Blank cells in the exclude list don't exclude anything, but a stray space in a name does stop it from matching.

## Questions

**How do I sum only the values that are in the list?**

Flip the test to `>0`: `=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)>0)*B2:B10)`.

**How do I exclude by part of a name?**

Use wildcards in SUMIFS: `=SUMIFS(B2:B10,A2:A10,"<>*air*")`.

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/sum-values-not-in-a-list