How to sum lookup results for a list of items in Excel
Excel formula
=SUM(XLOOKUP(A2:A6,C2:C10,D2:D10))Look up the whole list at once and add the results: =SUM(XLOOKUP(A2:A6,C2:C10,D2:D10)).
- Result on the example
- 16 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
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
Given the whole list A2:A6 as its lookup value, XLOOKUP returns five credit values, one per course: 4, 3, 3, 2 and 4. SUM adds them, 16, with no helper column.
SUMIF with a range of criteria does the same in any version: it returns one total per course, and SUMPRODUCT adds those up. That is the Google Sheets version here.
=SUM(XLOOKUP(A2:A6,C2:C10,D2:D10))Result16
A2:A65 rows × 1 col: "BIO-210", "MAT-150", "ENG-102", "ART-105", …A range: 5 cells in one column.
C2:C109 rows × 1 col: "ACC-101", "BIO-110", "BIO-210", "CHM-120", …A range: 9 cells in one column.
D2:D109 rows × 1 col: 3, 4, 4, 4, …A range: 9 cells in one column.
XLOOKUP(A2:A6,C2:C10,D2:D10){5 rows × 1 col} 4, 3, 3, 2, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
A2:A65 rows × 1 col: "BIO-210", "MAT-150", "ENG-102", "ART-105", … - lookup_array
C2:C109 rows × 1 col: "ACC-101", "BIO-110", "BIO-210", "CHM-120", … - return_array
D2:D109 rows × 1 col: 3, 4, 4, 4, …
- lookup_value
SUM(XLOOKUP(A2:A6,C2:C10,D2:D10))16SUM: Adds numbers and ranges. Skips text and blanks.- number1
XLOOKUP(A2:A6,C2:C10,D2:D10){5 rows × 1 col} 4, 3, 3, 2, …
- number1
In Google Sheets
Google Sheets needs a different formula: =SUMPRODUCT(SUMIF(C2:C10,A2:A6,D2:D10)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(SUMIF(C2:C10,A2:A6,D2:D10)) | 16 | Works in every Excel version and in Google Sheets. A course that isn't in the catalog counts as 0 instead of returning an error. |
Common mistakes
- A typo in the list makes the XLOOKUP version return #N/A, which is useful as a warning. The SUMIF version silently adds 0.
- A course listed twice is counted twice, which is usually right for an order and wrong for a schedule.
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 lookup results for a list of items
F2, total the credits of the courses selected in A2:A6, using the catalog in C2:D10.Questions
How do I multiply by quantities before adding?
Use SUMPRODUCT: =SUMPRODUCT(B2:B6,XLOOKUP(A2:A6,C2:C10,D2:D10)).
How do I skip unknown items instead of erroring?
Give XLOOKUP a 0 for not found: =SUM(XLOOKUP(A2:A6,C2:C10,D2:D10,0)).
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.