Buzz
The Sumif and Vlookup functions are widely used in Excel, and combining them is common. However, understanding the purpose, strengths, and weaknesses of using these functions is not known to everyone.
Combining Sumif and Vlookup Functions: Sumif combined with Vlookup
Before diving into the specifics of the Sumif and Vlookup functions, as well as utilizing the combination of Sumif and Vlookup, let's briefly revisit these two functions for those who have previously worked with Excel. If you're a beginner in Excel, this article is worth bookmarking and exploring Sumif and Vlookup.
What is the Sumif Function?
The Sum function allows you to calculate the sum of values in a column. However, in some cases, you may want to exclude certain values from the range. In such situations, the Sumif function is used as a substitute for Sum. With conditions added to the range, the Sumif function enables users to filter out values that do not meet the specified conditions and calculate the sum of the remaining values.
Sumif Formula: SUMIF(range, Criteria, sum_range)
Range: The selected range containing the condition cells.
Criteria: The condition to apply for this function.
Sum_range: The area to calculate the sum.
To have a better understanding of the SUMIF function and specific examples, readers can refer to the introduction article on SumIF compiled by Mytour.
What is the Vlookup Function?
Unlike the Sum Function, the Vlookup function does not perform any calculations but rather searches for data. It allows users to look up data in a specific range based on given conditions so that the displayed result meets the specified criteria. Typically, the Vlookup function in Excel is used to search for various codes such as student IDs or employee IDs, making it convenient for data retrieval.
Vlookup Function Formula:
Lookup_value: The value used for searching.
Table_array: The lookup table, specified as an absolute address (with a $ sign in front, achieved by pressing F4).
Col_index_num: The column number to retrieve data from the lookup table.
Range_lookup: The search range, TRUE is equivalent to 1 (relative search), FALSE is equivalent to 0 (exact search).
For more details about the Vlookup function and examples to further understand this function for beginners in Excel, readers can refer to the article on Vlookup function mentioned by Mytour.
Sumif and Vlookup Functions, How to Combine Sumif and Vlookup in Excel
Utilizing the combination of Sumif and Vlookup allows us to quickly retrieve any target data, providing the most accurate results. This approach eliminates the need to re-enter formulas when switching between different objects. Mastering the advanced Excel lesson of using Sumif and Vlookup in tandem is highly beneficial.
To delve deeper into the integration of Sumif and Vlookup, let's explore specific examples. Understanding the usage of Sumif and Vlookup involves examining three data tables.
In the list table, we observe three tables.
Table 1: Includes names and employee codes.
Table 2: Contains employee codes and their sales figures.
Table 3: Consists of names and sales figures (currently empty without data).
The task requires outputting the results to table 3, including names and the corresponding achieved sales figures. Additionally, it should be possible to look up the sales figures of other employees by changing the corresponding names.
Step 1: In this task, we will use the Sumif and Vlookup functions to calculate the total sales of the specified employee under the given conditions.
In this scenario, using the Sumif function alone cannot yield results because the sales values are in the Employee Code column, not in Table 1, and are unrelated to Table 2. Therefore, we need to use the Vlookup function to search for the corresponding employee code and then combine it with the Sum function to calculate the total with the condition being the matching employee code.
The formula for this task is: =SUMIF(D:D, VLOOKUP(G7, A7:B13, 2, FALSE), E:E)
- Sumif and Vlookup are function names for calculating totals and condition-based lookup, respectively.
- D:D is the selected range to contain condition cells.
- G7 is the value compared with the sales column, serving as the lookup value. When you change the name, the sales column also changes accordingly.
- A7:B13 is the column order to retrieve data for the lookup value G7.
- The number 2 is the order of the output value, displayed on the screen based on the number of columns for data retrieval; since the Employee Code column is in the second position, we set it to 2.
- False indicates an exact search range for accurate results, instead of using True for a relative result.
Step 2: Enter the formula into cell H7, which is the first value in the sales column in table 3. Note that you also need to enter the employee's name to search in Column G7. Here, we will calculate the total sales of the employee Mễ Thế Minh.
Step 3: The sales result is 7,250,000, corresponding to the total sales achieved by that employee. You can double-check manually.
Step 4: To be sure, change the name in Table 3, the name column, to another employee like Lê Tiến Lùi to get an accurate result as well.
Mytour has just completed guiding you on how to use the Sumif and Vlookup functions, combining these two functions for computation and data retrieval. Both Sumif and Vlookup are fundamental functions in Excel commonly used in various problem-solving scenarios and daily tasks. Apart from these two functions, there are many other basic functions in Excel such as Min, Max, etc. Hopefully, this guide partially helps you understand how to use Sumif combined with Vlookup.
Additionally, for those using Sum or Sumif, it's recommended to explore the guide on how to calculate the sum of filtered values in Excel by Mytour. Calculating the sum of filtered values in Excel is a method that enhances your Excel skills by understanding advanced functions.
Have you ever heard about combining the Vlookup and IF functions? If not, refer to the article on the combination of Vlookup and IF here to learn a new approach in utilizing Excel calculation functions.
