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.
Method 1 – Using SUMPRODUCT with VLOOKUP
In this example, we aim to calculate the total sum of salaries for employees within the IT department.
Step 1: Select any cell, for example, C18, and type the following formula:
=SUMPRODUCT(–(D7:D16=”IT”) * VLOOKUP(“IT”, D7:E16, 2, FALSE))
Step 2: Press Enter to get the sum of salaries of employees working in the IT department.
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.
Step 1: Select any cell, for instance, C18, and enter the following formula:
=SUM(FILTER(C2:C11, B2:B11=”IT”))
Step 3: Press Enter and you will get the sum of all the salaries of an IT department.
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:
Step 1: In any cell, for instance in F7, type the following formula:
=IF(D7=”IT”, VLOOKUP(“IT”, D7:E16, 2, FALSE), 0)
Step 2: Drag the formula for all the rows.
Step 3: In a cell F17, enter the following formula:
=SUM(F7:F16)
Step 3: Press Enter and you will get the sum of the salaries of employees working in the IT department.
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:
