Excel Combine XLOOKUP with SUM: 2 Ways

Formulas & functions · Updated November 22, 2026

Yes, you can combine XLOOKUP with SUM in Excel by nesting XLOOKUP inside SUM to sum a returned array, or by using SUM with XLOOKUP to sum multiple lookup results. The most common approach is =SUM(XLOOKUP(lookup_value, lookup_array, return_array)), which sums all values returned by XLOOKUP when it returns an array.

Why Combine XLOOKUP and SUM?

XLOOKUP is a modern lookup function that finds a value in a range and returns a corresponding value from another range. On its own, it returns a single value or an array if you give it a multi-column return range. SUM adds numbers together. Combining them lets you do things like: look up a product and sum all its monthly sales, or look up a department and sum all its expenses across multiple columns. This is a common need in financial reports, sales dashboards, and inventory tracking.

Before XLOOKUP, users often used VLOOKUP with SUMIFS or INDEX-MATCH. XLOOKUP is simpler because it handles arrays natively and doesn’t require the lookup column to be the first column of the table.

How to Use SUM with XLOOKUP (Basic Example)

Suppose you have a table with product names in A2:A10 and monthly sales in B2:B10 (Jan), C2:C10 (Feb), and D2:D10 (Mar). You want to find the total sales for “Widget” across all three months.

In cell F2, enter:

=SUM(XLOOKUP("Widget", A2:A10, B2:D10))

This returns the sum of all values in the row where A2:A10 equals “Widget”. For example, if Widget has sales 100, 150, 200, the formula returns 450.

If you have the lookup value in a cell (e.g., E2), use:

=SUM(XLOOKUP(E2, A2:A10, B2:D10))

This works because XLOOKUP returns an array of three numbers, and SUM adds them.

Summing Multiple Lookup Results (Array Formula)

Sometimes you need to sum values from multiple rows that match a condition. For example, you have a list of orders with customer names in A2:A20 and order amounts in B2:B20. You want the total for “Acme Corp”. You can use:

=SUM(XLOOKUP("Acme Corp", A2:A20, B2:B20))

But wait—XLOOKUP only returns the first match. If there are multiple orders for Acme Corp, this formula returns only the first amount. To sum all matching amounts, you need a different approach: use SUMIF or SUMIFS.

However, XLOOKUP can return an array if you give it an array of lookup values. For example, to sum amounts for a list of customers in E2:E4:

=SUM(XLOOKUP(E2:E4, A2:A20, B2:B20))

This returns an array of the first match for each customer, then sums them. But if a customer appears multiple times, it still only gets the first match. For true multiple matches, use SUMIFS.

Using XLOOKUP to Return a Range for SUM

Another powerful use: XLOOKUP can return a range (like a row or column) that you then sum. For example, you have a table where row 1 contains months (B1:D1) and rows 2-10 contain product names in column A. To sum all sales for “Widget” across all months, you can use:

=SUM(XLOOKUP("Widget", A2:A10, B1:D1))

But careful—this returns the header row (months) if your lookup array is A2:A10 and return array is B1:D1. That would sum text values, which SUM ignores, resulting in 0. Instead, you want the row of data for Widget. So you should use:

=SUM(XLOOKUP("Widget", A2:A10, B2:D10))

This returns the entire row of sales for Widget, which SUM adds.

When to Use SUMIFS Instead of XLOOKUP+SUM

If you need to sum values based on a condition across multiple rows (not just the first match), use SUMIFS. For example, to sum all orders for “Acme Corp”, use:

=SUMIFS(B2:B20, A2:A20, "Acme Corp")

This sums every matching row. XLOOKUP+SUM is best when:

  • You need to sum a row or column of values that correspond to a single lookup value (like a product’s monthly sales).
  • You want to sum values from multiple columns for one match.
  • You need to perform a lookup and sum in one formula without helper columns.

For multiple matches across rows, SUMIFS is the correct tool.

Common Errors and How to Fix Them

Here are typical problems you might encounter:

  • #N/A error: The lookup value isn’t found. Check for typos or extra spaces. Use the optional [if_not_found] argument: =SUM(XLOOKUP(E2, A2:A10, B2:D10, 0)) to return 0 instead of an error.
  • #VALUE! error: XLOOKUP returns text or blanks that SUM can’t handle. Ensure the return range contains only numbers.
  • Sum returns 0: The return range may be the wrong size or offset. Double-check the row and column references.
  • XLOOKUP returns only first match: As noted, if you need to sum multiple rows, use SUMIFS.

Frequently Asked Questions

Can I use XLOOKUP to sum multiple columns for a single lookup value?
Yes, as shown above, set the return_array to a multi-column range. XLOOKUP returns all values in that row, and SUM adds them.

Does XLOOKUP work with SUMIFS together?
You can combine them, but it’s rare. For example, =SUMIFS(XLOOKUP(...), ...) is not typical. Usually you use one or the other. If you need to sum based on a condition, use SUMIFS directly.

What if my lookup value is repeated and I want to sum all matches?
Use SUMIFS instead of XLOOKUP+SUM. XLOOKUP only returns the first match by default.

Can I use XLOOKUP with SUM in Excel 2019 or older?
No. XLOOKUP is only available in Excel 2021 and Microsoft 365. In older versions, use INDEX-MATCH with SUM or SUMIFS.

How do I sum values from a lookup that returns a dynamic array?
If XLOOKUP returns a spilled array (e.g., using XLOOKUP(E2:E4,...)), SUM will add all the values in that array. This works in Excel 365 and 2021.

Alternatives to XLOOKUP+SUM

Depending on your task, these alternatives may be better:

  • SUMIFS: Sum values based on one or more criteria across rows.
  • SUMPRODUCT: Sum values based on conditions, and can handle arrays without Ctrl+Shift+Enter.
  • INDEX-MATCH with SUM: For older Excel versions, use =SUM(INDEX(B2:D10, MATCH(E2,A2:A10,0), 0)) to sum a row.
  • FILTER function (Excel 365): =SUM(FILTER(B2:B20, A2:A20="Acme Corp")) sums all matching rows.

Each has its place. For a single lookup and summing a row, XLOOKUP+SUM is concise. For multiple criteria, SUMIFS or SUMPRODUCT are more flexible.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.