What Is VLOOKUP With SUM In Google Sheets?
VLOOKUP with SUM in Google Sheets helps us calculate the total of the numeric values, using the SUM(), that are the values retrieved based on the matching criteria’s by the VLOOKUP().
VLOOKUP in Google Sheets, helps users retrieve only the desired data or the lookup value from a large dataset. The SUM in Google Sheets calculates the total of the numeric values. We combine these two functions to find the Google Sheets VLOOKUP with SUM results. For example, we will find the total sales of an employee for the months of May and June, using the Google Sheets VLOOKUP with SUM function.
Select cell B9, enter the formula =SUM(VLOOKUP(A9,A2:C6,{2,3},0)) and press “Ctrl+Shift+Enter” to execute the formula as an array.
The output is shown above. The total sales by Rachel for 2 months is $27,000.
Key Takeaways
- The VLOOKUP with SUM in Google Sheets is a lookup function that helps us retrieve multiple column values and then the sum of those numeric values.
- VLOOKUP can fetch the value from more than one column if we specify the column numbers in curly brackets.
- VLOOKUP will not return the sum from multiple rows. Then, we use the SUMPRODUCT function to match the criteria and get the sum from the matching rows.
- We must remember not to press “Enter” to execute the VLOOKUP +SUM formula, because we will only get the first value and not the sum of all the values. Therefore, we must execute the formula as an array formula, by pressing the keys “Ctrl+Shift+Enter”.
Syntax
We do not have an inbuilt VLOOKUP with SUM in Google Sheets syntax in MS Excel or Google Sheets. However, we can build the syntax by combining the VLOOKUP and the SUM functions, as shown below.
Therefore, the syntax of the VLOOKUP with SUM formula in Google Sheets will be,
=SUM(VLOOKUP(search_key, range, index, [is_sorted]))
The arguments of the VLOOKUP with SUM formula in Google Sheets are,
- search_key: It is the value searched in the table’s first row. It is a mandatory argument.
- range: It refers to the cell range of the dataset from which the data is retrieved. It is a mandatory argument.
- index: It refers to the column number in the “range”. If “index” is set to 1, 2 and so on, the function returns the value from the first column, the second column and so on from the range. It is a mandatory argument.
- [is_sorted]: The “is_sorted” parameter determines whether the function should perform an approximate or exact match. A value of TRUE (1) indicates an approximate match, while a value of FALSE (0) indicates an exact match. If neither match is found, the function returns the #N/A error. It is an optional argument.
- Finally, the total of the retrieved numeric values will be calculated as the result of the argument of the Sum function.
How To Use VLOOKUP With SUM In Google Sheets?
We can use the VLOOKUP With SUM in Google Sheets as follows:
Step 1: Select an empty cell for the output. Go to Step 2.
Step 2: Type =SUM(VLOOKUP we will get the following syntax. It is as shown below.
Step 3: Enter the arguments. It is entered as cell values or cell references.
Step 4: Close double brackets, for both the SUM and the VLOOKUP functions.
Step 5: Press “Ctrl+Shift+Enter.” This is a must to execute the formula as an array.
Examples
We will consider some VLOOKUP with SUM in Google Sheets examples for the following dataset that consists of employee details such as their names, ID’s, Date of Joining, department and their half-yearly sales, from January 2024 to June 2024.
Example #1 – VLOOKUP to SUM Values in First Matched Row.
The steps to VLOOKUP to SUM Values in First Matched Row are as follows:
Step 1: Select cell C14 and enter the formula =SUM(VLOOKUP(B14,B2:K11,{5,6,7,8,9,10},0)), as shown below.
Step 2: Press “Ctrl+Shift+Enter” to execute as an array, to get the following result.
The output is shown above. The highlighted row is the matched row and the sum of the values is the result in cell C14, as $179,867.
Example #2 – VLOOKUP to SUM Values in All Matched Rows.
The steps to VLOOKUP to SUM Values in All Matched Rows are as follows:
Step 1: Select cell J2 and enter the formula =SUMPRODUCT((B2:B11=I2)*F2:G11), as shown.
Step 2: Press “Enter”, to get the following result.
The output is shown above. The highlighted rows are the matched rows and the sum of the values is the result in cell J2, as $133,690. Here, the search_key is in cell I2, that is the criteria to determine the sum of sales.
Example #3 – VLOOKUP to SUM across multiple sheets.
The steps to VLOOKUP to SUM across multiple sheets are as follows:
Step 1: Select cell B2 and enter the formula =SUM(VLOOKUP(B2, in the worksheet with the name “Ex 3 Sheet 2”, as shown below.
Step 2: To select the range argument, go to the worksheet with the name “Ex 3 Sheet 1” and select the cell range B2:K11, as shown below. Now, the complete formula will be =SUM(VLOOKUP(A2,’Ex 3 Sheet 1′!B2:K11
Step 3: Let us complete the formula by entering the index and the [is_sorted] values, as shown below.
Step 4: The complete formula is =SUM(VLOOKUP(A2,’Ex 3 Sheet 1′!B2:K11,{5,6,7,8,9,10},0)). Finally, press “Ctrl+Shift+Enter” to execute as an array, to get the following result.
We see the output in the “Ex 3 Sheet 2” worksheet and the sum of the values is the result in cell B2, as $195,614. The highlighted row is the matched row as displayed in the “Ex 3 Sheet 1” worksheet, as shown below.
Example #4 – VLOOKUP to SUM matching values in columns.
The dataset given below consists of fruits and their prices for the months of August to October. We will lookup the matching values in the columns and the find their total.
The steps to VLOOKUP to SUM matching values in columns are as follows:
Step 1: Select cell G2 and enter the formula =SUM(VLOOKUP(F2,A2:D8,{2.4},0), as shown below.
Step 2: Press “Ctrl+Shift+Enter” to execute as an array, to get the following result.
The output is $1000. The highlighted values are the matching values in the columns with the index 2 and 4. To check for other values we must first check if there exist any matching prices, and change the index values, accordingly.
Important Things To Note
- We must place the lookup value in the first column for the proper working of VLOOKUP formula.
- Ensure to give the right index numbers to the columns we must use. For example, the search_key column is indicated with 1, the next column with 2, and so on.
- To enter multiple column numbers or multiple index values in the VLOOKUP function, we must enter them inside the curly brackets ({}), each column number separated by a comma, like {2,3} for the index argument.
- The array formula will have the curly brackets on either side of the formula that is not visible in edit mode.
Frequently Asked Questions (FAQs)
The SUM in Google Sheets is found as follows:
Select the “Insert” tab – click the “Function” option right arrow – click the “Function” option right arrow – click the “Math” option right arrow – select the “SUM” function, as shown below.
The VLOOKUP function in Google Sheets is found as follows:
Select the “Insert” tab – click the “Function” option right arrow – click the “Lookup” option right arrow – select the “VLOOKUP” function, as shown below.
We often forget in which category a function falls, here, the “VLOOKUP” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “VLOOKUP” function, as shown below.
We often forget in which category a function falls, here, the “SUM” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “SUM” function, as shown below.
A few reasons VLOOKUP with SUM may not work are,
a. We have executed the formula with the keys “Ctrl+Enter” and have got the “#REF” error. We must remember to execute an array function with the keys “Ctrl+Shift+Enter”.
b. We have not given the Match value as 0 or 1 to get the exact or the approximate match.
c. We have entered an incorrect index number, due to which the column number is incorrect.
d. We have missed out on entering double brackets at the end of the formula.
Download Template
This article must help understand the VLOOKUP With SUM In Google Sheets with formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is VLOOKUP With SUM In Google Sheets. We learn how to use it to calculate the sum of the retrieved values using VLOOKUP. You can learn more from the following articles –
Leave a Reply