How to look up a value and sum all matches in Excel
Formula (Excel and Google Sheets)
=SUMIF(A2:A9,F1,C2:C9)Lookup functions stop at the first match, so use SUMIF: =SUMIF(A2:A9,F1,C2:C9) adds every row for that ID.
- Result on the example
- $2,740 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
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.
=SUMIF(A2:A9,F1,C2:C9)Result2740
A2:A98 rows × 1 col: "C-101", "C-104", "C-102", "C-104", …A range: 8 cells in one column.
F1"C-104"The value in F1.
C2:C98 rows × 1 col: $420, $1,250, $310, $880, …A range: 8 cells in one column.
SUMIF(A2:A9,F1,C2:C9)2740SUMIF: Adds values where the range meets a condition.- range
A2:A98 rows × 1 col: "C-101", "C-104", "C-102", "C-104", … - criteria
F1"C-104" - sum_range
C2:C98 rows × 1 col: $420, $1,250, $310, $880, …
- range
In Google Sheets
The same formula works in Google Sheets: =SUMIF(A2:A9,F1,C2:C9). We ran it in Google Sheets mode on the same data and it returns $2,740.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(FILTER(C2:C9,A2:A9=F1)) | $2,740 | Excel 365 and Google Sheets: FILTER returns every matching amount and SUM adds them. |
| =VLOOKUP(F1,A2:C9,3,FALSE) | $1,250Different result | 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.
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.
Look up a value and sum all matches
F2, total every order amount for the customer ID in F1.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").
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.