Seems like the combination of Sumif with Vlookup in Google Sheets piques your interest, right? Well, let me tell you, it’s a powerful tool that can enhance your data analysis skills and make your life easier. So, buckle up and get ready to dive into the world of Sumif and Vlookup!
Hiểu rõ về Sumif và Sum
Before we jump into the intricacies of using Sumif with Vlookup, let’s quickly recap the difference between Sum and Sumif.
When you want to calculate the total of a range of cells, you can use the Sum function. However, if you need to sum only specific cells that meet certain criteria, that’s where Sumif comes in handy. For example, let’s say you want to sum the total sales of a particular salesperson or a few salespersons. In that case, you can rely on Sumif to get the job done.
Let’s illustrate this with an example. In the following scenario, we have a dataset of salespeople and their sales numbers. The Sum formula in cell C12 sums the total sales in the range C3:C11. On the other hand, the Sumif formula in cell F3 sums the sales in the range C3:C11 if the ID in B3:B11 matches “EMP1001”.
If you’ve grasped the difference between Sum and Sumif, then you’re ready to take things further.
Note: Want to quickly master all the popular Google Sheets functions? Check out my free tutorials on Google Sheets functions here and become a pro user.
Tại sao việc kết hợp Sumif với Vlookup hữu ích trong Google Sheets?
In the Sumif formula we saw earlier, the criterion was “EMP1001” which was hardcoded in cell E3. But what if we want to use a name as the criterion instead of an ID? That’s where Vlookup comes to the rescue.
Let’s say we have a separate table, which we can call a “lookup table,” that contains unique IDs and their corresponding names. We can use Vlookup to search for a specific name in the lookup table and fetch the connected ID. This ID can then be used as the criterion in Sumif.
To illustrate this concept further, let’s take a look at an example. In the following scenario, we have a lookup table that connects IDs with names:
We can use Vlookup to search for the name “Ann” in the lookup table and fetch the corresponding ID, which is “EMP1001”. We can then use this ID as the criterion in our Sumif formula.
In summary, the combined use of Sumif with Vlookup in Google Sheets allows us to use Vlookup as the criterion for Sumif. This opens up a world of possibilities for analyzing and summarizing data based on specific criteria.
Now, let’s take a deep dive into this combined use of Sumif with Vlookup in Google Sheets.
Gán ID cho các tên trong Google Sheets
As mentioned earlier, we can use Vlookup to assign IDs to names and use these IDs as criteria in Sumif. Let’s break down the process step by step.
- Create a separate table, the lookup table, that contains unique IDs and connected names.
- Use Vlookup to search for a specific name in the lookup table and fetch the connected ID.
- Use this fetched ID as the criterion in the Sumif formula.
Let’s see an example to make things clearer.
Assume we have the name “Ann” and we want to assign the ID “EMP1001” to her. We can use the following Vlookup formula to achieve this:
=vlookup(E3,$H$3:$I$5,2,0)
This formula searches for the name “Ann” in the lookup table (range $H$3:$I$5) and fetches the corresponding ID.
We can then use this Vlookup formula as the criterion in our Sumif formula as follows:
=sumif($B$3:$B$11, vlookup(E3,$H$3:$I$5,2,0) ,$C$3:$C$11)
Now, if you want to get the sales total of “Mary,” simply insert her name and drag the formula down.
Sumif với nhiều tiêu chí sử dụng Vlookup trong Google Sheets
When dealing with multiple criteria in Sumif, you don’t always have to rely on the drag and drop formula from the previous example. Vlookup can search for multiple names and return corresponding multiple IDs. We can use an array of Vlookup formulas as criteria in Sumif.
To achieve this, use the following formula:
=ArrayFormula(vlookup(E3:E4,$H$3:$I$5,2,0))
In this formula, cell E3 contains the name “Ann” and cell E4 contains the name “Mary”. The above array formula will return the IDs “EMP1001” and “EMP1003,” respectively.
With this array of IDs, you can now use the following formula to perform the Sumif operation:
=ArrayFormula(sumif($B$3:$B$11, vlookup(E3:E4,$H$3:$I$5,2,0) ,$C$3:$C$11))
Insert this formula in cell F3 and watch the magic happen. If you encounter a #REF! error, make sure the cells below are blank.
That’s all there is to know about the combined use of Sumif with Vlookup in Google Sheets. I hope you find these techniques valuable and enjoy exploring the endless possibilities they offer.
Now, go ahead and unleash the power of Sumif and Vlookup in your Google Sheets!
Remember, if you want to learn more about Google Sheets functions and become a pro user, check out my tutorials on Crawlan.com.
Happy analyzing!
