Seems the combined use of Sumif with Vlookup in Google Sheets is something intriguing, right? So my first priority is to make you understand the relevance of such a combination.

We use Sumif over Sum when we want to include a condition from a column (Sumifs for conditions from multiple columns) to sum.

For example, we can get a total of sales using the Sum function. But when we want to sum the total sales of a particular salesperson or a few salespersons, we can depend on Sumif.

Let me quickly explain this Sum and Sumif difference for newbies.

In the following example, the Sum formula in cell C12 sums the total sales in the range C3:C11 whereas the Sumif in cell F3 sums the sales in the range C3:C11 if ID in B3:B11 matches “EMP1001”.

If you could understand both of the above formulas, you can proceed further.

Note: Want to quickly learn all popular Google Docs Sheets functions? Please check my Google Sheets functions guide (free tutorials) and become a pro user.

How the Sumif with Vlookup Combination is Useful in Google Sheets?

In the above Sumif formula, the criterion is “EMP1001” which is in cell E3.

Syntax: SUMIF(range, criterion, [sum_range])

It makes sense as the ‘range’ (the ‘range’ which is tested against ‘criterion’) contains IDs.

Think about a different scenario. Here both the ‘range’ and the ‘sum_range’ are the same (earlier ones). But the ‘criterion’ is a name instead of an ID.

Assume the ID “EMP1001” is assigned to “Ann” (In a table in the same tab or different tab. I’ll come to that).

How to use “Ann” as the criterion instead of “EMP1001”.

You will agree that the easiest way to remember an employee is by his/her name instead of IDs. So let’s learn how to assign IDs to names using Vlookup.

Assigning IDs to Names in Google Sheets

Create a separate table (we can call ‘lookup table’) that contains unique IDs and connected names. Use that lookup table in Sumif using Vlookup.

We can use Vlookup to search the name “Ann” in the lookup table and fetch the connected ID. That ID can be used as the criterion in Sumif.

This way we can use Vlookup to assign IDs to names and use it as the criterion in Sumif.

In concise, in the combined use of Sumif with Vlookup in Google Sheets, we can use Vlookup as the Sumif criterion.

Let me explain this Sumif and Vlookup combination in detail below.

As I have already explained, in the Sumif with Vlookup combination use, the Vlookup acts as the criterion.

For example, takes the criterion “Ann”. Use Vlookup to search this key in the Lookup table and fetch the corresponding ID.

Syntax:
VLOOKUP(search_key, range, index, [is_sorted])
Formula:
=vlookup(E3,$H$3:$I$5,2,0)

This formula would return “EMP1001”. Use this Vlookup as the criterion in Sumif as below.

=sumif($B$3:$B$11, vlookup(E3,$H$3:$I$5,2,0) ,$C$3:$C$11)

To get the sales total of “Mary” insert her name and drag the formula down.

Sumif Multiple Criteria with Vlookup in Google Sheets

When Sumif has multiple criteria as above, it’s not necessary to depend on a drag and drop formula as above.

Vlookup can search multiple names and can return corresponding multiple IDs. We can use Vlookup array as Sumif criteria.

=ArrayFormula(vlookup(E3:E4,$H$3:$I$5,2,0))

Must Read: How to Use Vlookup to Return An Array Result in Google Sheets.

Cell E3 contains the name “Ann” and cell E4 contains the name “Mary”. The above Vlookup multiple criteria would return the IDs “EMP1001” and “EMP1003” respectively.

So the Sumif Vlookup multiple criteria formula will be as follows.

=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. If you see #REF! error make sure that the cells below are blank.

That’s all on the combined use of Sumif with Vlookup in Google Sheets. Enjoy!