Excel XLOOKUP function to sum all matches

In Excel, the XLOOKUP function is a powerful tool that allows you to find and retrieve data from a table. One of the lesser-known features of the XLOOKUP function is the ability to sum all matches in a table. This tutorial will show you how to use the XLOOKUP function to sum all matches in Excel.

First, let’s start with a simple example. Suppose you have a table that lists the sales figures for different products in different regions:

Now, suppose you want to calculate the total sales for product A. You could use the SUMIF function to do this:

=SUMIF(A2:A5, "A", C2:C5)

This formula would sum the sales figures in column C for all rows where column A is equal to “A”.

However, what if you have a table with a lot of different products and regions? It could be time-consuming to create a separate SUMIF formula for each product.

This is where the XLOOKUP function comes in handy. You can use the XLOOKUP function to find all the rows in the table that match a specific product and then sum the sales figures for those rows.

Here’s the formula you can use:

=SUM(XLOOKUP("A", A2:A5, C2:C5, "", 0))

Let’s break down this formula:

• The first argument is the lookup value, which is “A” in this case.
• The second argument is the lookup array, which is the range A2:A5. This tells Excel where to look for the lookup value.
• The third argument is the return array, which is the range C2:C5. This tells Excel what data to return when it finds a match.
• The fourth argument is the not found value. In this case, we leave it blank because we want Excel to return 0 if it doesn’t find a match.
• The fifth argument is the match mode. We set this to 0 to find exact matches.

The XLOOKUP function returns an array of values that match the lookup value. We then use the SUM function to sum those values.

In our example, the XLOOKUP function returns the following array:

{100, 200}

And the SUM function sums those values to give us the total sales for product A:

=SUM({100, 200})
=300

This formula will work even if there are multiple rows in the table that match the lookup value. For example, if we want to calculate the total sales for product B, we can use the same formula:

=SUM(XLOOKUP("B", A2:A5, C2:C5, "", 0))

And this formula will return:

=SUM({150, 100})
=250

In conclusion, the XLOOKUP function is a powerful tool that can help you find and sum all matches in a table in Excel. By using the XLOOKUP function in combination with the SUM function, you can save time and avoid repetitive formulas.