Often you may want to use the XLOOKUP function to look up some value in a range and then sum the corresponding values in another column for all matches.
Unfortunately, the XLOOKUP function in is only capable of returning the value for the first match.
However, you can use the SUM and FILTER functions instead to look up some value in a range and sum the corresponding values in another range for all matches:
=SUM(FILTER(C2:C11, E2=A2:A11))
This particular formula looks in the range C2:C11 and returns the corresponding values in the range A2:A11 for all rows where the value in C2:C11 is equal to E2.
The following example shows how to use this syntax in practice.
Example: Use XLOOKUP to Sum All Matches in Excel
Suppose we have the following dataset in Excel that shows the points scored by basketball players on various teams:
Suppose we would like to look up the value in “Mavs” in the Team column and sum the values in the Points column for all matches.
We can use the following syntax to do so:
=SUM(FILTER(C2:C11, E2=A2:A11))
The following screenshot shows how to use this formula in practice:
We can see that the sum of all values in the Points column for each occurrence of “Mavs” in the Team column is 71.
We can verify this is correct by manually calculating the sum of all values in the Points column for each row where the Team column is “Mavs”:
Sum of Mavs Points: 20 + 39 + 12 = 71
This matches the value calculated by our formula.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use XLOOKUP with Wildcard
Excel: How to Use XLOOKUP From Another Sheet
Excel: How to Use XLOOKUP with IF Statement
