How to sum values that are not in a list in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10)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).
- Result on the example
- $1,088 in
F2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10)Result1088
D2:D32 rows × 1 col: "Delta Air", "Marriott"A range: 2 cells in one column.
A2:A109 rows × 1 col: "Delta Air", "Office Hub", "Marriott", "Cloudly", …A range: 9 cells in one column.
COUNTIF(D2:D3,A2:A10){9 rows × 1 col} 1, 0, 1, 0, …COUNTIF: Counts cells that meet one condition.- range
D2:D32 rows × 1 col: "Delta Air", "Marriott" - criteria
A2:A109 rows × 1 col: "Delta Air", "Office Hub", "Marriott", "Cloudly", …
- range
COUNTIF(D2:D3,A2:A10)=0{9 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
B2:B109 rows × 1 col: $1,240, $186, $845, $299, …A range: 9 cells in one column.
COUNTIF(D2:D3,A2:A10)=0)*B2:B10{9 rows × 1 col} 0, 186, 0, 299, …The * operator multiplies.
SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10)1088SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
COUNTIF(D2:D3,A2:A10)=0)*B2:B10{9 rows × 1 col} 0, 186, 0, 299, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((COUNTIF(D2:D3,A2:A10)=0)*B2:B10). We ran it in Google Sheets mode on the same data and it returns $1,088.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(B2:B10,A2:A10,"<>"&D2,A2:A10,"<>"&D3) | $1,088 | One "not equal" condition per excluded value. Fine for two or three values. |
| =SUM(FILTER(B2:B10,COUNTIF(D2:D3,A2:A10)=0)) | $1,088 | 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.
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.
Sum values that are not in a list
F2, total the amounts for every vendor that is not in the exclude list D2:D3.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*").
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.