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.

**Table of Contents**Expand

**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**:

`=SUM(FILTER(D5:D14,C5:C14=F5))`

**⧪**** 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:

`=SUM(IF(C5:C14=F5,D5:D14,""))`

**[**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:

`=D5&COUNTIF($D$5:D5,D5)`

**⧪ 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.

**Ampersand Symbol (&)**concatenates two strings].

**⧪**** 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:

`=SUM(VLOOKUP(G5&ROW(B1:INDIRECT("B"&COUNTIF(D5:D14,G5))),B5:E14,4,FALSE))`

**[**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**(**Autho**r) 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}**.

**Ampersand Symbol (&)**concatenates two strings].

**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**

Hello,

So I’m trying to create a sum of two separate columns with the vlook from two different columns equals a given name.

What I have is an excel spreadsheet that downloads from an external platform. The scenario is:

If column “K” is blank then I need the sum of column “N” for any rows with the label “asphalt field” in column “I” and if column “K” has a matching label then I need the sum of columns “j” & “L” that match the label?

I have been trying various different formula setups but this is way above my ability. please help!

Hi KEITH,Your problem is partly vague I think. Still, I’ve tried to build a formula that might work for you. If this doesn’t work, I would recommend you share your workbook with me or at least share a sneak peek of your dataset.Now use this formula:=IF(ISBLANK(K2),SUMIF(I2:I13,”asphalt field”,N2:N13),SUM(J2:J13,L2:L13))Thanks!