Home > Microsoft Office > Excel

How to sum all matches with VLOOKUP in Excel – 3 methods

Use our step-by-step guide to sum all matches in Excel.
Abdul Haddi
Last Updated on
How to sum all matches with VLOOKUP in Excel – 3 methods
PC Guide is reader-supported. When you buy through links on our site, we may earn an affiliate commission. Read More

Table of Contents

If you want to know how to sum all matches with VLOOKUP in Excel, you’ve landed on the right page.

In this guide, we’ll explore three straightforward methods. First, we’ll dive into the approach of summing matches using the SUMPRODUCT with VLOOKUP function. Then, we’ll introduce the practical applications of using the FILTER function to sum all matches. Finally, we will leverage conditional summing with VLOOKUP in Excel for a comprehensive understanding of diverse approaches.

So, without wasting another second, let’s dive in.

Today's best deals

*Stock availability and pricing subject to change depending on retailer or outlet.

Method 1 – Using SUMPRODUCT with VLOOKUP

In this example, we aim to calculate the total sum of salaries for employees within the IT department.

A spreadsheet that matches salaries with a list of employees.

Step 1: Select any cell, for example, C18, and type the following formula:

=SUMPRODUCT(–(D7:D16=”IT”) * VLOOKUP(“IT”, D7:E16, 2, FALSE))

A screenshot of a spreadsheet in excel showcasing the use of VLOOKUP to find matches and perform sum calculations.

Step 2: Press Enter to get the sum of salaries of employees working in the IT department.

An excel spreadsheet with a number of rows and columns that includes the SUM function and VLOOKUP for finding matches.

How does this formula work?

This formula uses VLOOKUP in combination with SUMPRODUCT to sum salaries based on the IT department.

  • (D7:D16=”IT”): This part checks each cell in the range D7:D16 to identify if the department is “IT” and produces a TRUE/FALSE array.
  • VLOOKUP(“IT”, D7:E16, 2, FALSE): The VLOOKUP function looks for the department “IT” in the range D7:E16 and retrieves the corresponding salary from the second column.
  • –(D7:D16=”IT”) * VLOOKUP(“IT”, D7:E16, 2, FALSE): The TRUE/FALSE array is multiplied by the corresponding salary, resulting in an array where salaries are considered only for rows where the department is “IT.”

SUMPRODUCT(…): Finally, the SUMPRODUCT function adds up all the values in the array obtained from the multiplication, effectively summing the salaries for employees in the “IT” department.

Method 2 – Using FILTER function to sum all matches with VLOOKUP

In this example, we aim to calculate the total sum of salaries for employees within the IT department. The FILTER function, in combination with VLOOKUP, will be utilized to selectively extract and sum the salaries associated with the specified department.

A spreadsheet that matches salaries with a list of employees.

Step 1: Select any cell, for instance, C18, and enter the following formula:

=SUM(FILTER(C2:C11, B2:B11=”IT”))

An excel spreadsheet with a number of rows and columns, including the ability to sum values and use VLOOKUP to find matches.

Step 3: Press Enter and you will get the sum of all the salaries of an IT department.

An excel spreadsheet with a number of rows and columns that includes the function VLOOKUP to find matches.

How does this formula work?

This formula uses the FILTER function to get an array of salaries where the department is “IT,” and then the SUM function adds up all these values.

  • The FILTER function matches a lookup value (“IT”) with all values in the lookup column B2:B11 (Department).
  • It returns the corresponding values from the column C2:C11 (Salary).
  • The overall formula, SUM(FILTER(C2:C11, B2:B11=”IT”)), calculates the total sum of salaries for employees within the IT department.
  • By adjusting the lookup value, you can dynamically calculate the total salary for employees in different departments.

Feel free to adapt this example to your specific data and criteria. The FILTER function is particularly useful for dynamic data extraction based on specified conditions.

Method 3 – Conditional summing with VLOOKUP in Excel

Consider the same data set:

A spreadsheet that matches salaries with a list of employees.

Step 1: In any cell, for instance in F7, type the following formula:

=IF(D7=”IT”, VLOOKUP(“IT”, D7:E16, 2, FALSE), 0)

A spreadsheet with a number of rows and columns, used for summing data and performing VLOOKUP matches.

Step 2: Drag the formula for all the rows.

An Excel spreadsheet showing the sum of employees in a workbook using VLOOKUP.

Step 3: In a cell F17, enter the following formula:

=SUM(F7:F16)

An excel spreadsheet with a number of employees, utilizing VLOOKUP and sum functions to match and calculate data.

Step 3: Press Enter and you will get the sum of the salaries of employees working in the IT department.

Learn how to create a spreadsheet in Excel with useful functions such as VLOOKUP and sum. Discover how to effectively use the matches feature for accurate data analysis.

How does this formula work?

This approach uses an IF statement with VLOOKUP to return the salary for employees in the IT department and 0 for others. The final SUM function then adds up all these values. Feel free to adjust the example based on your dataset and specific criteria. VLOOKUP offers flexibility in retrieving data, and combining it with other functions can lead to diverse solutions.

Conclusion

In this guide, we’ve discussed three different ways to sum all matches with VLOOKUP in Excel. The entire process may seem complicated at first, but you’ll be able to do everything within minutes once you get the hang of it. All three methods work perfectly and will help you get your desired results.

Learn more about Excel with these in-depth helpful guides:

About the Author

Abdul Haddi

Abdul is a tech writer and Editor for PC Guide, specializing in all things tech, gaming, and hardware.