Table of Contents
XLOOKUP is a powerful function in Excel that allows users to search for specific values within a range and return corresponding values from another range. It can also be used to sum all matches found within a range, making it a useful tool for data analysis and calculation purposes.
To use XLOOKUP to sum all matches in Excel, you first need to specify the lookup value, the array to search in, and the array to return values from. Then, you can use the optional “sum_range” argument to specify the range of values that should be summed. This will return the total sum of all matches found within the specified range.
In addition, XLOOKUP also has the ability to handle multiple criteria, allowing for more complex and specific searches. This makes it a versatile and efficient function for summing all matches in Excel.
Excel: Use XLOOKUP to Sum All Matches
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
Additional Resources
The following tutorials explain how to perform other common operations in Excel: