Learn how to sum multiple rows or columns based on a lookup value you can use the SUM function with the XLOOKUP function.
Even if we have a definitive XLOOKUP guide, we learn something special daily. Today’s example will be on how to find the matching value in a table and sum multiple rows or columns. The method we demonstrate completely replaces the recent VLOOKUP and HLOOKUP functions.
How to SUM multiple rows values based on a lookup value
This solution provides a powerful VLOOKUP alternative. Use a vertical lookup to find the matching value and sum multiple columns in the same row.
For the sake of simplicity, we will use named ranges:
- Products = B3:B9
- Data = C3:E9
Configure the XLOOKUP function arguments:
- lookup_value: G3
- lookup_array: “products”
- return_array: “data”
Formula:
=XLOOKUP(G2, products, data)
The result is a dynamic array that contains three numeric values.
We use the SUM function to compile the array into one cell because we want to get the result as a single value instead of an array.
Formula:
=SUM(XLOOKUP(G2, products, data))
Steps to SUM multiple column values based on a lookup value
The following example is based on a horizontal lookup and replaces the HLOOKUP function. First, create a horizontal lookup formula to find the matching value and sum multiple rows in the same column.
Our goal is to find the sales in 2022. In this case, we want to find the matching value in the header section.
Create a new named range; “year” will refer to range C2:E2.
Formula:
=XLOOKUP(G2, year, data)
The formula will search for the lookup value in the header and return all column values in case of a match. As we stated before, the result is an array containing all values we want to sum. We want to return the sum, so combine the XLOOKUP function with the SUM function.
Formula:
=SUM(XLOOKUP(G2, year, data))
The result is a single value.
Related Formulas
- Lookup entire row or column
- Lookup value between two numbers