To add the values returned by XLOOKUP, wrap the lookup formula in SUM: with prices in B2:B5, matching product codes in A2:A5, and requested codes in D2:D4, use =SUM(XLOOKUP(D2:D4,A2:A5,B2:B5,0)). If the three requested codes are P-101, P-103, and P-104, and their prices are 12, 15, and 5, the result is 32.
What SUM and XLOOKUP do together
XLOOKUP finds a value in one range and returns the corresponding value from another. SUM adds numbers. Nesting XLOOKUP inside SUM is useful when you have a list of keys to look up and want the total of the corresponding results, rather than a separate answer for each key.
For the opening example, the source table could contain P-101 and 12, P-102 and 8, P-103 and 15, and P-104 and 5 in columns A and B. The lookup list in D2:D4 contains P-101, P-103, and P-104. XLOOKUP returns the three prices as an array, and SUM adds that array to produce 32. The formula’s fourth argument, 0, tells XLOOKUP to return zero for a code it cannot find.
This works in Excel for Microsoft 365 on Windows. XLOOKUP is also available in Excel 2021 and later, including Microsoft 365 editions for Mac and the web. It is not available in Excel 2019 or Excel 2016, so an older workbook may need an alternative formula.
How the formula works, step by step
In XLOOKUP, the first argument is what to find, the second is where to search, and the third is the range containing the results. The optional fourth argument specifies what to return when there is no match. For example, with the product table in A2:B5 and one requested code in D2, this formula returns 12 when D2 contains P-101:
With product codes in A2:A5, prices in B2:B5, and one requested code in D2, use:
=SUM(XLOOKUP(D2,A2:A5,B2:B5,0))
For that one lookup, SUM is not necessary: XLOOKUP already returns a single price. The wrapper becomes useful when the lookup value is a range, such as D2:D4, because XLOOKUP can return one result for each requested code and SUM can add those results. The source lookup range and return range should be aligned: each code in A2:A5 must correspond to the value on the same row in B2:B5.
When a formula returns an array, Microsoft 365 and Excel 2021 can calculate the array directly inside SUM. You do not need to enter it as a legacy array formula with Ctrl+Shift+Enter. If you remove SUM and enter the XLOOKUP by itself, Excel may spill its multiple results into adjacent cells, provided those cells are empty.
Adding several requested values
The multi-value version is especially handy when a short list of selected items should be totaled from a longer reference table. With product codes in A2:A5, prices in B2:B5, and requested codes in D2:D4, enter this formula in E2:
=SUM(XLOOKUP(D2:D4,A2:A5,B2:B5,0))
If D2:D4 contains P-101, P-103, and P-104, the lookup returns 12, 15, and 5. The sum is 32. If D3 instead contains P-102, Excel returns 12, 8, and 5, which total 25. The formula updates when a code in the requested list changes.
You can use a larger lookup list, but it should have the same number of entries as the set of items you intend to total. A repeated code in the request list is counted again. For instance, if P-101 appears twice in D2:D4, its price of 12 contributes twice. That is correct when the list represents two purchases, but not when the list is supposed to contain unique product codes.
For a changing list, you can reference a whole column or an Excel Table column, but be deliberate about blank cells and the range size. A blank lookup value can match a blank in the source data, and whole-column calculations may involve more cells than necessary. A table also makes the formula easier to read because its references can use column names and expand as records are added.
Multiple matches are different from multiple lookups
A common misunderstanding is that XLOOKUP returns every source row that matches a key. By default, it returns one match: the first match in the search direction. Therefore, SUM(XLOOKUP(…)) adds results for multiple requested lookup values; it does not total every source record sharing one requested value.
For example, suppose column A contains region names and column B contains sales. If “North” appears in several rows, looking up North with XLOOKUP returns only the value from the first matching row. To add all sales for North, use SUMIF instead. With regions in A2:A6 and sales in B2:B6, where the North rows contain 100, 75, and 25:
=SUMIF(A2:A6,"North",B2:B6)
The result is 200. SUMIF is designed to total values meeting one criterion. For several criteria, such as a region and a product, SUMIFS is usually the direct choice. With regions in A2:A6, products in B2:B6, and sales in C2:C6, this adds sales for North and Widget:
=SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Widget")
Use SUM with XLOOKUP when you have specific keys and want the associated single result for each key. Use SUMIF or SUMIFS when you want every qualifying row in the source data included.
Choosing between SUM, SUMIF, and XLOOKUP
The right formula depends on how the question is phrased and how the data is structured:
- One key, one corresponding value: XLOOKUP returns the value. Wrapping it in SUM usually adds no benefit.
- A list of keys, one value per key: SUM(XLOOKUP(…)) looks up each key and totals the returned values.
- All records matching one condition: SUMIF totals the matching rows, including repeated keys.
- All records matching multiple conditions: SUMIFS applies multiple criteria to the source rows.
- A workbook without XLOOKUP: INDEX and MATCH can retrieve a single corresponding result, though handling an array of lookup keys may require a different formula design.
XLOOKUP is often easier to maintain than older lookup approaches because its search and return ranges are specified separately, and the return range can be to either side of the search range. It also uses exact matching by default. However, it is not a substitute for a conditional sum: deciding whether to total selected lookups or all matching records is more important than choosing the newest function.
Common errors and how to fix them
- #N/A: A requested code was not found, and the formula did not provide an alternate result. Include 0 as XLOOKUP’s fourth argument if treating missing codes as zero is appropriate. If a missing code should be investigated, do not silently replace it with zero.
- Unexpected total: Check whether a code appears more than once in the request list. Each appearance produces a result that SUM includes. Also check whether the source table contains duplicate keys: XLOOKUP returns the first matching row, not the total of those rows.
- Zero instead of a visible price: Check that the returned values are real numbers rather than text that looks like numbers. Convert text-formatted numeric data to numbers before relying on a total, and check for spaces or inconsistent code formatting.
- Wrong match: XLOOKUP defaults to exact matching, but values that look identical may differ because one is stored as text, contains an extra space, or uses a different code format. Compare the source and requested values, not just their appearance.
- #VALUE! or an invalid range: Make sure the lookup range and return range have compatible dimensions and refer to corresponding rows. A vertical lookup range should normally pair with a vertical return range of the same height.
During setup, test a few requested codes individually and compare the results with the source table. This is a simple way to catch an incorrectly selected range or an unexpected duplicate before trusting the final total.
Making the formula easier to maintain
For a small, fixed table, ordinary cell references are easy to inspect. If the source data grows, convert it to an Excel Table by selecting a cell in the data and choosing Insert > Table. Tables expand when rows are added and can make formulas more readable through structured references. Confirm that the table columns contain consistent key and value data, and use the same column references throughout the formula.
Keep the requested keys in a clearly labeled range rather than typing many values into a formula. That makes it easier to review what is being totaled, change the selection, and spot accidental duplicates. If you need each distinct requested code only once, remove duplicates from the input list or create a unique list before summing; do not assume XLOOKUP automatically deduplicates its lookup values.
Also decide how missing keys should behave. Returning zero is convenient if a missing item genuinely contributes nothing. But a zero can hide a spelling error or absent record. In a reconciliation or audit, it may be safer to let the missing match show as an error or flag missing entries separately, then resolve them before accepting the total.
Frequently Asked Questions
Can SUM add the results of multiple XLOOKUPs?
Yes. Give XLOOKUP a range of lookup values and wrap it in SUM. Excel returns a result for each requested value, and SUM adds them. Duplicate requested values are counted repeatedly.
Does XLOOKUP return all rows with the same value?
No. A standard XLOOKUP returns one match, normally the first matching row. Use SUMIF or SUMIFS if the goal is to add every source row that meets one or more criteria.
Why does SUM(XLOOKUP(…)) return #N/A?
At least one lookup value may have no match. Add an appropriate if-not-found result as XLOOKUP’s fourth argument, such as 0 when zero is valid, or inspect missing values rather than masking them.
Can I use SUM with XLOOKUP in Excel 2019?
No. XLOOKUP is not available in Excel 2019 or Excel 2016. Use a compatible alternative such as INDEX and MATCH for individual lookups, or SUMIF/SUMIFS when totaling all rows that meet criteria.
Is SUM(XLOOKUP(…)) the same as SUMIF?
No. SUM with XLOOKUP totals the results for the supplied lookup values, generally one match per value. SUMIF totals all source values whose corresponding criteria cells meet the condition.
Related
- Excel Maximum Row Height: The 409-Point Limit
- Excel Max Date: What Is the Latest Date Excel Supports?
- Excel Max Absolute Value: Find the Largest Magnitude
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Median Formula Examples in Excel and by HandThe median is the middle value after a set of numbers is arranged from smallest to largest; with…
- Excel SEQUENCE Examples for Numbers, Dates, and MoreExcel’s SEQUENCE function creates a spill range of consecutive numbers or other values: enter =SEQUENCE(5) in a blank…
- Excel IFS Function: Syntax, Examples, and Common ErrorsExcel’s IFS function contains pairs of logical tests and the values to return when those tests are true.…
- Sequence Excel Column: Create Numbers with SEQUENCETo create a sequence of numbers down an Excel column, enter =SEQUENCE(10) in the first cell; in Microsoft…
- Excel Concatenate Formula Examples for Text and CellsWith a first name in A2 and a last name in B2, join them with a space using…
- Excel Time Difference in Hours: Formulas and ExamplesTo calculate the difference between two Excel times in decimal hours, subtract the start time from the end…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.