# SUMIF With VLOOKUP In Google Sheets

## What Is SUMIF With VLOOKUP In Google Sheets?

The SUMIF with VLOOKUP in Google Sheets is a SUMIF formula, with the VLOOKUP() supplied as its dynamic criterion argument. The VLOOKUP() helps retrieve the return value for the specified lookup value to ensure the SUMIF() totals the rows of data based on the return value cited as the criterion.

Users can use the SUMIF with VLOOKUP formula in Google Sheets when the lookup value, given as a criterion, is in another dataset. The other dataset can be in the same or different sheet as the main table containing the values to add conditionally.

For example, we have two datasets. The first is the main one containing the sales representative IDs and their date-wise sales-generated data. On the other hand the second is a lookup table, which lists the sales representatives and their IDs.

We must determine the total sales generated by the sales representative, cited in cell F9, and display the output in cell F10. Also, since the target cell must show a currency value, it has the same data type format as cells C2:C11.

Then, we can apply the SUMIF(), with the VLOOKUP() as its second argument, in the target cell to secure the required outcome. The logic will be inline with the meaning of SUMIF with VLOOKUP In Google Sheets explained earlier.

We must add the column C sales values pertaining to the sales representative Timothy Smith. So, we can search the representative’s name in the first dataset to add the applicable sales values using the SUMIF().

However, the main table lists the representatives’ IDs instead of their names, with the sales representatives’ names and IDs provided in another table. So, we achieve the output by using SUMIF with VLOOKUP in Google Sheets formula, which works with the similar logic as SUMIF with VLOOKUP in Excel.

Furthermore, the SUMIF() and the VLOOKUP() individually work as the Excel SUMIF function and Excel VLOOKUP function.

The SUMIF() accepts three inputs, with the second argument, criterion, being the VLOOKUP().

First, the VLOOKUP() looks for the sales representative name in the second dataset. Since it finds a match in cell E5, the function returns the corresponding ID in the cell in the 5th row of column F, which is the cell F5 value, STF113.

So now, the SUMIF() tests the condition, which is the VLOOKUP() output value of STF113 in the range A3:A12. It finds the matches in cells A6, A9, and A12. Next, the function adds the sales values in rows 6, 9, and 12 of column C, which are the cells C6, C9, and C12 values.

Thus, the formula SUMIF with VLOOKUP in Google Sheets returns \$7,600 as the required total sales generated by the specified sales representative.

###### Key Takeaways
• The SUMIF with VLOOKUP in Google Sheetsis a formula where we utilize the VLOOKUP() to provide a dynamic criterion argument value to the SUMIF function. It ensures the SUMIF function returns the correct output even when the search term, cited as a criterion, is not in the source table but in a separate lookup table.
• The SUMIF with VLOOKUP formula in Google Sheets helps when we must add values based on a specific data point. However, the data point is not in the main table. Instead, we must look up the data point in another table to get a return value, which is the SUMIF() condition.
• Assume we must check multiple criteria to add the required values. Then, we must use the SUMIFS() instead of the SUMIF() in the SUMIF-VLOOKUP formula in Google Sheets.

### Syntax

The SUMIF-VLOOKUP formula in Google Sheets syntax is the following:

Where,

• range: The range that we need to test against the condition, which in this case is the VLOOKUP() output.
• VLOOKUP(): The function output is the criterion argument value. It is the condition that the SUMIF() checks for in the range. The VLOOKUP function arguments are the following:
• search_key: The value to search for in the first column of the lookup range.
• range: The top and bottom limits of the lookup range.
• index: The index of the column in the specified range containing the return value. Also, it is a positive number.
• is_sorted: The value denotes whether the VLOOKUP() should look for an exact or approximate match.
• FALSE (0): The recommended value representing an exact match search.
• TRUE (1): It represents an approximate match search. The function considers it as the default value if we ignore the argument.

Also, in the scenario of an approximate match, the lookup value range should be sorted in ascending order. Otherwise, the function output may be incorrect.

• sum_range: The range containing the values to add based on the given condition if it is different from the specified range.

Furthermore, we must supply all the argument values when using SUMIF with VLOOKUP in Google Sheets, except for the last ones in the two functions, as they are optional.

### How To Use SUMIF With VLOOKUP In Google Sheets?

The steps to apply the SUMIF with VLOOKUP formula in Google Sheets are as follows:

1. Choose the cell where we aim to display the result.
2. Type =SUMIF( in the cell. [ Alternatively, type =S or =SU and click the function name SUMIF from the listed options to select it.]
3. Supply the first argument and enter a comma. Next, type VLOOKUP(. Otherwise, type V or VL and click the function name VLOOKUP from the listed options to select it.) After that, supply the VLOOKUP() argument values, separated by commas, and close the bracket. Finally, enter a comma and the SUMIF()’s last argument to update the SUMIF With VLOOKUP In Google Sheets arguments, and close the bracket.
4. Press Enter to acquire the formula output.

### Examples

The following examples describe the practical ways of using the SUMIF-VLOOKUP formula, adhering to the concept of SUMIF With VLOOKUP In Google Sheets explained earlier.

#### Example #1 – Use Of SUMIF(VLOOKUP) Together To Determine Some Value

The main table shows the product codes and their store-wise inventory level data.

We need the total inventory level of the product, cited in cell F8, to show the value in cell F9.

The issue is that we can not look up the product name in the main table to determine the required total inventory level of the specific product. The reason is that the first table lists product codes and not the names. However, we have a separate lookup table mapping the product names and their codes.

In such a case, we can implement the SUMIF-VLOOKUP formula in the target cell to secure the desired outcome.

Step 1: Select the target cell F9 and enter the SUMIF().

=SUMIF(

Enter the first argument, range, value, which is A3:A13, followed by a comma.

The second argument is the criterion, which is the VLOOKUP(). So, enter the function name and the opening bracket.

Now, enter the first argument, search_key, value in the VLOOKUP(), which is cell F8, followed by a comma.

The next argument in the function is the range, which is the lookup range of E3:F6, and enter a comma.

After that, we must supply the third argument, index, value. In this case, we need the VLOOKUP() to return the product ID. So, the argument value is 2.

Next, enter a comma and as we must find an exact match, enter the fourth argument, is_sorted, value as 0. Close the bracket and enter a comma.

Finally, we will enter the last argument, sum_range, in the SUMIF(), which is the range holding the inventory level data, C3:C13. So, now we have entered all the SUMIF with VLOOKUP In Google Sheets arguments. Next, close the bracket to complete the formula.

=SUMIF(A3:A13,VLOOKUP(F8,E3:F6,2,0),C3:C13)

Step 2: Press Enter to execute the SUMIF-VLOOKUP formula in the chosen cell.

First, the VLOOKUP() looks for the product iPhone 15, in the range E3:F6. It finds the exact match in cell E4. So, the function returns the value in the cell where row 4 and column F intersect, which is the cell F4 value, IP_350.

Next, the SUMIF() searches for the product code IP_350, which the VLOOKUP() returned as the condition in the range A3:A13. The function locates the exact matches in cells A5, A6, A12, and A13. So, the function adds the inventory level values in the cells in the corresponding rows of column C since the specified sum_range is C3:C13. So, the function adds the cells C5, C6, C12, and C13 values and the SUMIF With VLOOKUP In Google Sheets returns 4100 as the total inventory level of the product specified in cell F8.

#### Example #2 – Determining Sum Based On Matching Criteria In Different Work Sheets

Consider that we have two sheets. While the first one contains the source dataset, the second contains the lookup table.

The source dataset in the first sheet lists the marketing activity codes, and the corresponding date-wise payment received data. On the other hand, the lookup table in the second sheet contains the list of marketing activities and their codes.

The requirement is to determine the total payment received for the marketing activity specified in cell F1 and show the output in cell F2 in the first sheet. Please note the target cell data type is the same as the range C3:C12, Currency.

Then, the steps to implement the SUMIF-VLOOKUP function in the target cell to secure the anticipated result are as follows:

Step 1: Choose cell F2 and enter the SUMIF().

=SUMIF(

Enter the first argument value, A3:A12, followed by a comma.

Next, enter the VLOOKUP() as the criterion argument value.

We will enter the first argument in the VLOOKUP(), which is the search key, the cell reference to the value in cell F1, followed by a comma.

The second argument in the VLOOKUP() is the range, which is in the second sheet. So, click the second sheet tab to open the second sheet and choose the range A3:B8 as the lookup range.

Next, enter a comma.

After that, supply the third argument, index, value. In this case, we need the VLOOKUP() to return the marketing activity code. So, the argument value is 2.

Next, enter a comma and since we should find an exact match, enter the fourth argument, is_sorted, value as 0. Close the bracket and enter a comma.

After that, we shall enter the last argument, sum_range, in the SUMIF(), which is the range containing the payment received data, C3:C12. Finally, close the bracket.

=SUMIF(A3:A12,VLOOKUP(F1,’Marketing Activity – Code Table’!A3:B8,2,0),C3:C12)

Step 2: Press Enter to implement the SUMIF-VLOOKUP formula in the selected cell.

First, the VLOOKUP() searches for the marketing activity, Infographics Creation, in the range A3:B8. It locates the exact match in cell A4. So, the function returns the value in the cell where row 4 and column B meet, which is the cell B4 value, MAR_12.

Next, the SUMIF() looks for the marketing activity code MAR_12 in the range A3:A12, which the VLOOKUP() returned as the criterion. The function finds the exact matches in cells A3, A7, and A10. So, the function adds the payment received values in the cells in the corresponding rows of column C since the specified sum range is C3:C12. Thus, the function adds the cells C3, C7, and C10 values to return \$1,350 as the total payment received for the marketing activity specified in cell F1.

### Important Things To Note

• The SUMIF and VLOOKUP functions in the SUMIF with VLOOKUP in Google Sheetsformula are not case sensitive.
• Assume the VLOOKUP() in the SUMIF-VLOOKUP formula does not find a match for the lookup value. Then, the VLOOKUP() returns the #N/A error, and the SUMIF-VLOOKUP formula output will be 0. Also, if the criterion specified in the SUMIF() is not met, the SUMIF-VLOOKUP formula returns 0 as the output.
• Assume the sum_range argument range holds only non-numeric values. Then, the SUMIF-VLOOKUP formula returns 0 as the output.

How to apply SUMIF with VLOOKUP for multiple criteria in Google Sheets?

We can apply SUMIF with VLOOKUP for multiple criteria in Google Sheets using the SUMIFS() instead of the SUMIF() since the formula must check multiple conditions.

For example, there are two datasets. The first is the main table containing the client IDs and their date-wise units ordered data. The second is the lookup table holding the list of client names and their IDs.

The requirement is to evaluate the total units ordered by the client cited in cell I2 on dates after the date specified in cell I3. Assume the target cell is I5.

We can secure the required outcome using the SUMIFS() containing the VLOOKUP() as its criterion argument.

Step 1: Select cell I5, enter the following SUMIFS(), and press Enter.

=SUMIFS(C2:C13,A2:A13,VLOOKUP(I2,E2:F6,2,0),B2:B13,”>”&I3)

The first argument in the SUMIFS() is the range containing the values the function must add based on the specified conditions.

Next, the second argument is the first condition’s range, against which we must test the first condition. The first condition is the VLOOKUP() output. It checks for the client name Carol Lewis in the lookup range E2:F6. It finds a match for the lookup value in cell E4. So, it returns the value in the corresponding row of column F, which is the cell F4 value, C207.

So, the SUMIFS() looks for an exact match for C207 in the range A2:A13, which it finds in cells A2, A6, A8, and A11.

Next, the second criterion range is B2:B13, and the condition is to check for dates after 15 June 2024. So, the SUMIFS() checks the cells in the corresponding rows of column B (cells B2, B6, B8, and B11), where the second condition holds.

The second condition is true in cells B6, B8, and B11. So, the SUMIFS() adds the values in the corresponding rows of column C, which are cells C6, C8, and C11 values, to return the value of 3050 as the required output.

How the SUMIF with VLOOKUP combination is useful in Google Sheets?

The SUMIF with VLOOKUP combination is useful in Google Sheets when the search value, cited as the condition in the SUMIF(), is not in the main table. However, it is available in a separate lookup table in the same of different sheet as the main table.

Why is SUMIF with VLOOKUP not working in Google Sheets?

The SUMIF with VLOOKUP is not working in Google Sheets because of the following reasons:

• The VLOOKUP() does not find a match for the lookup value and thus returns the #N/A error. So, the SUMIF-VLOOKUP formula returns the value of 0.
• The range containing the return value is on the left of the range the VLOOKUP() checks the lookup value for a match. In this case, the VLOOKUP() will not work, leading to the SUMIF-VLOOKUP formula not working or returning an incorrect value.