# How to look up a value and sum all matches in Excel

Lookup functions stop at the first match, so use SUMIF: `=SUMIF(A2:A9,F1,C2:C9)` adds every row for that ID.

**Excel:** `=SUMIF(A2:A9,F1,C2:C9)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Customer ID | Order | Amount |  | Customer ID | C-104 |
| C-101 | SO-3301 | 420 |  | Total |  |
| C-104 | SO-3302 | 1250 |  |  |  |
| C-102 | SO-3303 | 310 |  |  |  |
| C-104 | SO-3304 | 880 |  |  |  |
| C-103 | SO-3305 | 1460 |  |  |  |
| C-104 | SO-3306 | 95 |  |  |  |
| C-101 | SO-3307 | 730 |  |  |  |
| C-104 | SO-3308 | 515 |  |  |  |

Formula in F2: `=SUMIF(A2:A9,F1,C2:C9)`

Result: $2,740

## How it works

VLOOKUP and XLOOKUP return one value, from the first matching row. SUMIF checks every row of `A2:A9` against the ID and adds all the matching amounts: four orders here. It is the "lookup and add up" people usually want.

## Other ways

- `=SUM(FILTER(C2:C9,A2:A9=F1))`: Excel 365 and Google Sheets: FILTER returns every matching amount and SUM adds them.
- `=VLOOKUP(F1,A2:C9,3,FALSE)`: Shows what a lookup returns. (Only the first match: 1,250.)

## Common mistakes

- Wrapping VLOOKUP in SUM doesn't help: `=SUM(VLOOKUP(…))` still adds only the first match.
- If the IDs are numbers in one place and text in the other, SUMIF matches them anyway, but lookups don't. Keep IDs consistent.

## Questions

**How do I list the matching orders too?**

In Excel 365 or Google Sheets: `=FILTER(B2:C9,A2:A9=F1)`.

**How do I sum matches with a second condition?**

Use SUMIFS: `=SUMIFS(C2:C9,A2:A9,F1,C2:C9,">500")`.

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/look-up-and-sum-all-matches