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

**Table of Contents**hide

**Download Practice Workbook**

**3 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 of 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.

In the given data set, the formula to find out the sum of the **prices** of all the books of **Charles Dickens** will be:

`=SUM(FILTER(D4:D13,C4:C13=F4))`

**â§Ş**** 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
**F4**(**Charles Dickens**) is our**lookup value**,**C4:C13 (Author)**is the**lookup column**, and**D4:D13 (Price)**is the other column. **FILTER(D4:D13,C4:C13=F4)**matches all values of the column**C4:C13 (Author)**with**F4**(**Charles Dickens**) and returns the corresponding values from the column**D4:D13**(**Price**).- Finally,
**SUM(FILTER(D4:D13,C4:C13=F4))**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**F4**, and it will return the total price of the books of that author.

**Read More:** **How to Sum Filtered Cells in Excel (5 Suitable Ways)**

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

If you use an older version of Excel, you can use the **IF function** of Excel to sum 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(C4:C13=F4,D4:D13,""))`

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

**â§Ş**** Explanation of the Formula:**

**IF(C4:C13=F4,D4:D13,â€ťâ€ť)**matches all values of the**lookup column****C4:C13**(**Author**) with the**lookup value F4**(**Charles Dickens**).- If the
**lookup value F4**matches the**lookup column C4:C13**(**Author**), then it returns the corresponding value from the column**D4:D13**(**Price**). - And if it doesnâ€™t match, it returns a blank string
**â€śâ€ť**. - Finally,
**SUM(IF(C4:C13=F4,D4:D13,â€ťâ€ť))**returns the sum of all the values returned by the**IF function**.

**Read More:** **How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)**

**Similar Readings**

**How to Add Multiple Cells in Excel (6 Methods)****Excel Find Matching Values in Two Columns****How to Vlookup and Pull the Last Match in Excel (4 Ways)****Copy Values to Another Cell If Two Cells Match in Excel: 3 Methods****How to Match Data in Excel from 2 Worksheets**

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

âž¤ Select the adjacent column left to the data set and enter this formula in the first cell:

`=C4&COUNTIF($C$4:C4,C4)`

**â§Ş Note:**

- Here
**C4**is the first cell of the**lookup array**(**Author**). You use the one from your data set.

**â§Ş**** Step 2:**

âž¤ Drag the **Fill Handle **up to the last cell.

âž¤ 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 **F4**.

**â§Ş**** Step 4:**

âž¤ Finally, enter this formula in another cell:

`=SUM(VLOOKUP(F4&ROW(A1:INDIRECT("A"&COUNTIF(C4:C13,F4))),A4:D13,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(C4:C13,F4)**returns**3**, as there are a total of**3**cells in the range**C4:C13**(**Autho**r) that contain the**lookup value F4**(**Charles Dickens**). See the**COUNTIF function**for details.**A1:INDIRECT(â€śAâ€ť&COUNTIF(C4:C13,F4))**now becomes**A1:A3**. See the**INDIRECT function**for details.**ROW(A1:INDIRECT(â€śAâ€ť&COUNTIF(C4:C13,F4)))**becomes**ROW(A1:A3)**and returns an array**{1, 2, 3}**. See the**ROW function**for details.**F4&ROW(A1:INDIRECT(â€śAâ€ť&COUNTIF(C4:C13,F4)))**becomes**F4&{1, 2, 3}**and returns an array**{Charles Dickens1, Charles Dickens2, Charles Dickens3}**.

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

**VLOOKUP(F4&ROW(A1:INDIRECT(â€śAâ€ť&COUNTIF(C4:C13,F4))),A4:D13,4,FALSE)**now becomes**VLOOKUP({Charles Dickens1, Charles Dickens2, Charles Dickens3},A4:D13,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**A4:A13**, 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 (6 Methods)**

**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 (3 Examples)****Excel Sum If a Cell Contains Criteria (5 Examples)****Excel VBA: Copy Row If Cell Value Matches (2 Methods)****Excel VBA to Match String in Column (5 Examples)****How to Return Row Number of a Cell Match in Excel (7 Methods)****Sum Cells in Excel: Continuous, Random, With Criteria, etc.****How to Sum Multiple Rows and Columns in 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!