# How to sum lookup results for a list of items in Excel

Look up the whole list at once and add the results: `=SUM(XLOOKUP(A2:A6,C2:C10,D2:D10))`.

**Excel:** `=SUM(XLOOKUP(A2:A6,C2:C10,D2:D10))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=SUMPRODUCT(SUMIF(C2:C10,A2:A6,D2:D10))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Selected |  | Course | Credits |  | Total credits |
| BIO-210 |  | ACC-101 | 3 |  |  |
| MAT-150 |  | BIO-110 | 4 |  |  |
| ENG-102 |  | BIO-210 | 4 |  |  |
| ART-105 |  | CHM-120 | 4 |  |  |
| CHM-120 |  | ECO-101 | 3 |  |  |
|  |  | ENG-102 | 3 |  |  |
|  |  | MAT-150 | 3 |  |  |
|  |  | PSY-101 | 3 |  |  |
|  |  | ART-105 | 2 |  |  |

Formula in F2: `=SUM(XLOOKUP(A2:A6,C2:C10,D2:D10))`

Result: 16

## 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.

## Other ways

- `=SUMPRODUCT(SUMIF(C2:C10,A2:A6,D2:D10))`: 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.

## 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))`.

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-lookup-results-for-a-list