How to Sum All Matches with VLOOKUP in Excel (3 Easy Ways)

In this article, I shall show you how you can sum all matches with VLOOKUP in Excel. You’ll learn how to sum all the matches using the VLOOKUP function, along with the uses of the FILTER function and the IF function in combination with the SUM function. The final output that we will get will look like this image below.

3 Easy Ways to Sum All Matches with VLOOKUP in Excel

Here we’ve got a data set with the Names, Authors, and Prices of some books from a bookshop called Martin Bookstore.

Let’s try to find out the sum of all matches with VLOOKUP from this data set.

1. Use FILTER Function to Sum All Matches with VLOOKUP in Excel (For Newer Versions of Excel)

Those who have access to an Office 365 account, can use the FILTER Function of Excel to sum all matches from any data set.

First, in the given dataset, let us enter the formula to find out the sum of the prices of all the books by Charles Dickens:

⧪ Explanation of the Formula:

• The FILTER function matches a lookup value with all the values of a lookup column and returns the corresponding values from another column.
• Here F5 (Charles Dickens) is our lookup value, C5:C14 (Author) is the lookup column, and D4:D13 (Price) is the other column.
• Next, FILTER(D4:D13,C5:C14=F4) matches all values of the column C5:C14 (Author) with F5 (Charles Dickens) and returns the corresponding values from the column D4:D13 (Price).
• Finally, SUM(FILTER(D4:D13,C5:C14=F5)) returns the sum of all the prices of all the books returned by the FILTER function.
• You can change the lookup value to any other author except Charles Dickens in cell F5, and it will return the total price of the books of that author.

Read More: How to Vlookup and Pull the Last Match in Excel

2. Use IF Function to Sum All Matches with VLOOKUP in Excel (For Older Versions of Excel)

Now, if you use an older version of Excel, you can use the IF function of Excel, to sum up, all the matches from any data set.

The sum of the prices of all the books of Charles Dickens can be found using this formula:

[It is an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.]

⧪ Explanation of the Formula:

• IF(C5:C14=F5,D5:D14,””) matches all values of the lookup column C5:C14 (Author) with the lookup value F5 (Charles Dickens).
• If the lookup value F5 matches the lookup column C5:C14 (Author), then it returns the corresponding value from the column D5:D14 (Price).
• And if it doesn’t match, it returns a blank string “”.
• Finally, SUM(IF(C5:C14=F5,D5:D14,””)) returns the sum of all the values returned by the IF function.

Read More: How to Vlookup and Sum Across Multiple Sheets in Excel

3. Use VLOOKUP Function to Sum All Matches with VLOOKUP in Excel (For Older Versions of Excel)

You can also use the VLOOKUP function of Excel to sum all the values that match the lookup value.

⧪ Step 1:

➤ To begin with, select the adjacent column left to the data set and enter this formula in the first cell:

⧪ Note:

• Here D5 is the first cell of the lookup array (Author). You use the one from your data set.

⧪ Step 2:

➤ After that, drag the Fill Handle up to the last cell.

➤ As a result, it will create a sequence of the authors along with the ranks. Like Charles Dickens1, Charles Dickens2, Elif Shafak1, Elif Shafak2 and so on.

⧪ Step 3:

➤ Enter the lookup value in a new cell.

➤ Here I have entered Charles Dickens in cell G5.

⧪ Step 4:

➤ Finally, enter this formula in another cell:

[It is an Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.]

Look, it returns the sum of the prices of all the books of Charles Dickens, $52.00.

⧪ Explanation of the Formula:

• COUNTIF(D5:D14,G5) returns 3, as there are a total of 3 cells in the range D5:D14 (Author) that contain the lookup value G5(Charles Dickens). See the COUNTIF function for details.
• B1:INDIRECT(“B”&COUNTIF(D5:D14,G5)) now becomes B1:B3. See the INDIRECT function for details.
• G5&ROW(B1:INDIRECT(“B”&COUNTIF(D5:D14,G5))) becomes G5&{1, 2, 3} and returns an array {Charles Dickens1, Charles Dickens2, Charles Dickens3}.

• VLOOKUP(G5&ROW(B1:INDIRECT(“B”&COUNTIF(D5:D14,G5))),B5:E14,4,FALSE) now becomes VLOOKUP({Charles Dickens1, Charles Dickens2, Charles Dickens3},B5:E14,4,FALSE).
• The VLOOKUP function matches the lookup value with all the values of the first column of the data set and then returns the corresponding values from another column.
• Here the lookup value is the array {Charles Dickens1, Charles Dickens2, Charles Dickens3}.
• Therefore it matches the lookup values with all the values of the first column B5:E14, and returns the corresponding values from the 4th column (Price).
• Finally, the SUM function returns the sum of all the prices that match the lookup values.

Read More: How to Use VLOOKUP with SUM Function in Excel

Download Practice Workbook

Conclusion

Using these methods, you can sum all matches with VLOOKUP in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.

Related Articles

• Excel VBA to Match Value in Range
• How to Match Names in Excel Where Spelling Differ
• Excel VBA: Copy Row If Cell Value Matches
• Excel VBA to Match String in Column
• How to Return Row Number of a Cell Match in Excel
• How to Sum Top n Values in Excel

<< Go Back to Excel VLOOKUP Sum | Excel VLOOKUP Function | Excel Functions | Learn Excel