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

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

1. Select cell F2.
2. Type the formula: =SUMPRODUCT((A2:A10=F1)*(C2:C10))
3. 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 1s and 0s 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:

1. Select cell F2.
2. Type the formula: =SUMPRODUCT((A2:A10=F1)*(C2:C10))
3. 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:

1. Select cell F2.
2. Type the formula: =SUM(IF(A2:A10=F1,C2:C10,””))
3. 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:

1. Choose cell F2.
2. Write the formula: =SUM(FILTER(C2:C10,A2:A10=F1))
3. 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:

1. Select cell B10.
2. Type the formula: =SUM(VLOOKUP(B9, $A$2:$D$7, {2,3,4}, FALSE))
3. 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:

1. Select cell B10.
2. Type the formula: =SUM(INDEX(B2:D7,0,MATCH(B9,B1:D1,0)))
3. 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:

1. Select the cell where you want the result.
2. Enter the formula: =SUMIF(range, criteria, sum_range)
3. 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.