How to combine Sumif and Vlookup functions in Excel

For those who regularly handle Excel data tables and calculate data, it is certain to know two popular Excel functions: Sumif and Vlookup function. Function Sumif is used to calculate conditional values, while Vlookup function to search values ​​in arrays. And combining two Sumif functions and Vlookup function in Excel is very much applied.

The following article will introduce you how to combine Sumif and Vlookup functions in Excel.

1. Using Sumif Excel function:

When the Sum function allows the user to sum the values ​​of a certain data area, the Sumif function will help you to calculate the total range of data that ignores any value in the range. What kind of value do you have, using which value comes with the condition to calculate the sum.

Readers can refer to how to apply the Sumif function in the article How to use the SUMIF function in Excel.

2. How to use Vlookup function in Excel:

VLOOKUP function on Excel is used to search data in an array. The function allows users to look up data on a given string according to given conditions. Vlookup function is often used in the types of data tables to look up student code, employee code . Using Vlookup function in Excel is quite diverse, flexible, in which combining Sumif and Vlookup is a use case Pretty popular use.

3. How to combine Sumif and Vlookup functions:

For data tables that need to find objects, conditional data, Sumif and Vlookup functions will quickly search for more data, accurate results even when changing objects. Especially you don't need to retype the formula.

To better understand how to apply Sumif and Vlookup functions, we will have an example as shown below.

The revenue table below has 3 small tables with 3 different contents. Table 1 will be the employee with each person's code, Table 2 will be the employee number and the sales of each person. Table 3 will be the table filled in the results and left blank.

The content of that article is to enter the result of the employee's name as well as the sales of that person in Table 3 . Besides, you can look up the sales of other employees when changing their respective names.

Here you need to use Sumif and VLlookup functions , to calculate the total employee turnover under given conditions.

If we only use Sumif function, we cannot calculate the total revenue of the employee because the column SV code is not in the same table. Thus, you need to use VLOOKUP function to find employee code corresponding to each person, then combine Sumif function to calculate the total revenue of employees with conditions.

Step 1:

We will apply the formula to the table. The formula will be:

= SUMIF (D: D, VLOOKUP (B12, A3: B7,2, FALSE), E: E)

Inside:

1. Sumif and Vlookup are aggregate functions and conditional search functions.
2. D: D is the area containing the conditional cells.
3. B12 is the value area to compare with the sales column, the value to be searched. When changing the name, the sales column also changes.
4. A3: B7 is the column data area to retrieve data to detect the value for the B12 area above.
5. Number 2 is the order of output displayed on the screen, depending on how many columns the data needs to be taken. Here the column to get the data is Code NV in position 2, column B, so the order will be 2.
6. Flase is the absolute search range that gives accurate results instead of using True for relative results.
7. E: E is a given area for each employee's revenue.

Step 2:

You will enter the formula above in cell C12 in Table 3, then enter the employee name you want to calculate the total revenue in cell B12 . Here I will calculate the total revenue of the fee fee of Thanh Thanh Lan.

It will then display the total revenue that this employee achieved. Total amount is absolutely correct.

Note to users in case when calculating the total revenue and not displaying commas separating the row classes in the sequence, you can refer to the article How to separate thousands by commas in Excel to re-display the sign comma delimited numbers in Excel

We will need to change that column to Number format, then adjust to display comma delimited in Format Cells.Decimal places section we can customize depending on the number you need to calculate. To make it easier to look at the Sample section.

You can change the format of the column before or after the result is calculated.

Step 3:

Now you can change the name of any employee, no need to enter another calculator formula and still give the correct result. For example, I will enter cell B12 with the name of employee Tran Thu Ha with the employee number of MS01, the results of the search results still give accurate results.

Above is a detailed tutorial on how to combine Sumif and Vlookup functions on Excel. Sumif and Vlookup functions are computational functions, the very basic data search function on Excel. And combining 2 functions will help users find data faster, without manual calculation even if the search data is changed.

I wish you all success!