# How to Vlookup and Sum All Matches in Excel [5 Ways]

Follow these steps below to vlookup and sum all matches in Excel:

- Select cell
**F2.** - Type the formula:
**=SUMPRODUCT((A2:A10=F1)*(C2:C10))** - Press
**ENTER**to insert the formula.

This formula calculates the sum of values in column **C (C2:C10)** where the corresponding values in column **A (A2:A10)** match the value in cell **F1**.

## 1. Vlookup and Sum All Matches in Excel Using SUMPRODUCT Function

The **SUMPRODUCT** function is used to multiply corresponding elements in arrays or ranges and then sum the products of those multiplications. You can use the **SUMPRODUCT** function for various tasks, such as calculating weighted averages or performing conditional summing based on certain criteria in multiple arrays.

**Syntax**

**=SUMPRODUCT(array1, array2, ...)**

**Formula**

**=SUMPRODUCT((A2:A10=F1)*(C2:C10))**

**Formula Breakdown**

This formula checks if the values in cells **A2 **to **A10** are equal to the values in cell **F1**. It assigns a value of **1** if they are equal and **0** if they are not equal. Then, it multiplies these **1**s and **0**s by the corresponding values in cells **C2** to **C10**. Finally, it adds up all these products.

To vlookup and sum all matches in Excel, use the **SUMPRODUCT** function. The process is given below:

- Select cell
**F2.** - Type the formula:
**=SUMPRODUCT((A2:A10=F1)*(C2:C10))** - Press
**ENTER**to insert the formula.

**Final Results**

Finally, we find the summation of the values of all “**Samsung”** products.

## 2. Vlookup and Sum All Matches in Excel with SUMIF Function

You can use the **SUMIF** function in Microsoft Excel to perform conditional summing. This means you can sum up values from a range based on certain conditions or criteria.

**Syntax**

**=SUM(IF(condition_range, values_to_sum))**

**Formula**

=SUM(IF(A2:A10=F1,C2:C10,""))

**Formula Breakdown**

This formula adds up values in **C2:C10**, but only if the corresponding value in **A2:A10** matches the value in cell **F1**. Non-matching cells are represented by empty strings.

For vlookup and summing all matches in Excel, utilize the **SUMIF** function with the following steps:

- Select cell
**F2.** - Type the formula:
**=SUM(IF(A2:A10=F1,C2:C10,””))** - Press
**ENTER**to insert the formula.

** **

** ****Final Result**

Using this formula, you can efficiently compute the total price of **“Apple”** products.

## 3. Vlookup to Sum All Matches in Excel Using SUM & FILTER Functions

The combination of **SUM** and **FILTER **allows you to perform more advanced conditional summing compared to the basic **SUMIF** or **SUMPRODUCT** functions.

**Syntax**

=SUM(FILTER(array,include))

**Formula**

=SUM(FILTER(C2:C10,A2:A10=F1))

**Formula Breakdown**

This formula only includes values where the corresponding cells in the **A2:A10** range are equal to the value found in cell** F1**. The **FILTER** function isolates these matching values from **C2:C10**, and then the **SUM** function adds them up, providing the total sum of values that meet the specified condition in **A2:A10**.

For vlookup and summing all matches in Excel, employ the **SUM** and **FILTER** functions with the following steps:

- Choose cell
**F2.** - Write the formula:
**=SUM(FILTER(C2:C10,A2:A10=F1))** - Press
**ENTER**.

**Final Result**

In this method, our objective is to identify **Google’s** products and calculate the total price of these products. We achieve this by employing the **SUM** and **FILTER** functions, which ultimately yield the total price for all **Google** products.

## 4. Using the VLOOKUP Function to Sum All Matches in Excel

We have a list of electronics companies with columns for each month’s sales data. Now, I will use the **VLOOKUP **function to sum all matches. The procedure is given below.

**Syntax**

=SUM(VLOOKUP(lookup_value, table_index, col_index_num, [range_lookup]))

**Formula**

=SUM(VLOOKUP(B9, $A$2:$D$7, {2,3,4}, FALSE))

**Formula Breakdown**

This formula adds up values from the second, third, and fourth columns (**B, C**, and **D**) of a table **($A$2:$D$7)** based on a lookup in column **A** using the value in cell **B9**, with an exact match **(FALSE)**. It sums up values from the matched row in those three columns.

To perform a vlookup and sum all matches in Excel, use the **VLOOKUP** function following these steps:

- Select cell
**B10.** - Type the formula:
**=SUM(VLOOKUP(B9, $A$2:$D$7, {2,3,4}, FALSE))** - To insert the formula press
**ENTER**.

**Final Result**

Finally, the formula looks up a value in cell **B9** **(Samsung)** within a table, and when it finds a match, it sums up the values from February, March, and April from the corresponding row.

## 5. Vlookup and Sum All Matches in Excel Applying INDEX and MATCH Functions

In this method, I aim to create a formula in Excel using INDEX and MATCH functions that calculates the sum of a column based on specific criteria.

**Syntax**

=SUM(INDEX(array,row_number,MATCH(lookup_value,lookup_array,[match_type])))

**Formula**

=SUM(INDEX(B2:D7,0,MATCH(B9,B1:D1,0)))

**Formula Breakdown**

This formula calculates the sum of values in a column **(B2:D7) **based on a condition specified in the first row **(B1:D1)**. It matches the condition in cell **B9** with the first row and sums up the corresponding column values.

To achieve vlookup and sum all matches in Excel, use the **INDEX **and **MATCH** functions following these steps:

- Select cell
**B10.** - Type the formula:
**=SUM(INDEX(B2:D7,0,MATCH(B9,B1:D1,0)))** - Press
**ENTER**to insert the formula.

**Final Result**

This formula allows us to get the total sales for the month of** February**.

## Conclusion

I’ve demonstrated five ways to lookup vertically and sum all matches in Excel using various formulas. These formulas search a table for a particular value, and when they discover a match, they add the values.

## Frequently Asked Questions

### Does VLOOKUP count how many matches?

No, VLOOKUP in Excel does not inherently count the number of matches. It returns the first match it finds. To count the number of matches, you can use the COUNTIF function in combination with VLOOKUP. For example, **=COUNTIF(range, lookup_value)** will count the occurrences of the lookup value in the specified range.

### How do I sum all matching values in Excel?

To sum all matching values in Excel, use the **SUMIF** function. Here’s how:

- Select the cell where you want the result.
- Enter the formula:
**=SUMIF(range, criteria, sum_range)** - Press Enter to get the sum of matching values.

### Does VLOOKUP count how many matches?

Yes, **VLOOKUP** in Excel does not count how many matches.

It only returns the first matching value it finds in the specified range. If you need to count the number of matches, you can use the **COUNTIF** function with the same criteria as your **VLOOKUP**.

### Can VLOOKUP find multiple matches?

No, by default, **VLOOKUP** in Excel returns the first match it finds.

To find multiple matches, you can use alternative functions like** INDEX** and **MATCH **or consider using array formulas, pivot tables, or helper columns for more advanced solutions.

### Is VLOOKUP the first or last match?

**VLOOKUP** in Excel returns the first match it finds in the specified range. If there are multiple matches, it stops at the first one it encounters and does not search for subsequent matches.

To find the last match, consider using alternative functions like **INDEX** and **MATCH** or other advanced techniques.

### Can you have 2 criteria in a VLOOKUP?

No, **VLOOKUP** in Excel only allows for a single criterion.

If you need to use multiple criteria, consider using the **INDEX** and **MATCH** functions together or explore other advanced functions like **SUMIFS, COUNTIFS,** or database functions like **DCOUNT **and **DSUM**.