Simple and Clear Guide to Combine SUM and VLOOKUP in Google Sheet – 2024

Many people are currently struggling to combine SUM and VLOOKUP in Google Sheets. To help you overcome this difficulty, let’s explore it together in this article on the FPT Shop website.

How to use the SUM function in Google Sheets

What is the SUM function?

The SUM function is used to calculate the sum of numbers in a range of cells. The parameters can be a range of cells or single parameters. The result of the SUM function is the sum of the selected values in the cells.

The SUM function is used for checking and calculating the sum of numbers in a reference range. Currently, it can be combined with many other functions to support work, such as combining SUM and Vlookup or combining SUM and Query,…

Calculation formula

To calculate the SUM function, you use the following formula: =SUM(number1, [number2,…])

Where:

• Number 1: Is a number or a range of cells you want to add.
• Number 2: Is an additional number or range of cells you want to add.

Illustrative example

Question: Calculate the sum from cell B2 to B5 as in the image.

Answer: Apply the formula: =SUM(B2;B5). Then press ENTER to get the desired result.

How to use the Vlookup function in Google Sheets

What is the Vlookup function?

Vlookup is one of the basic functions and is often used to search values by column. The function allows users to search for values by column.

The VLOOKUP function is used to assist in looking up information in a data field or a list based on available identifiers. It can also be combined with many other functions to support work, such as combining SUM and Vlookup or combining with LEFT, RIGHT functions,…

Calculation formula

To calculate the VLOOKUP function, you use the following formula: =VLOOKUP(lookup_value; table_array, col_index_num; [range_lookup])

Where:

• Lookup_value: Is the value you will use to search.
• Table_array: Is the table area to search for values, the first column in “table_array” is the column to search for values and can be the same/different sheet as the “Lookup_value” or it can be the same/different file as the “Lookup_value”. Usually in absolute address format.
• Col_index_num: This is the range to search for the order of the column, you will get the data on the value table you want to find. In this case, the first column of the data range is estimated to be 1.
• Range_lookup: Determines whether the search is exact (FALSE) or approximate (TRUE or omitted).

Illustrative example

Question: Sort student types by score. After the final exam, we have the corresponding score of the exams for each student as in table 1, and rank based on score according to table 2 (B11:C15).

Answer: According to the table above, we have the formula: =VLOOKUP(D4,$B$11:$C$15,2,1). Then press ENTER to get the desired result as shown in the image.

How to combine SUM and Vlookup in Google Sheets

Calculation formula

The formula to combine the SUM and Vlookup functions is: =SUM(VLOOKUP(lookup_value; table_array; {2;3;4;..}; FALSE))

Where:

• Lookup_value: This is the value you will use to search.
• Table_array: This is the range where you search for values.
• {2;3;4;…}: Refers to the columns that contain the values you want to sum.
• FALSE: Determines whether the search is exact or not.

Illustrative example

Question: Combine SUM and VLOOKUP in Google Sheets to calculate the total production of DESKTOP.

Answer: We apply the formula combining SUM and VLOOKUP as follows: =ArrayFormula(SUM(VLOOKUP(A10;A1:E4;{2;3;4;5};0))). In which ArrayFormula is the command that allows the values returned from the array formula to be displayed in multiple rows.

Common errors when combining SUM and Vlookup

Here are some common errors you may encounter when combining the SUM and Vlookup functions.

Combination error #N/A

This error often occurs when the function cannot find the required data, the formula uses the object to be searched or the reference that does not exist in the lookup range. As a result, you cannot calculate and complete the formula. This error occurs when you use lookup or reference functions.

• Example: The error “#NA” occurs when you enter the wrong lookup object from “a4 to a2“.
• How to fix: When encountering this error, you just need to update the correct value of the lookup object. According to the image above, change from “a2 to a4“.

Combination error #REF!

During the process of combining SUM and Vlookup, you will see the error “#REF!“. This is an error display because the formula refers to an invalid cell. This error usually occurs when the referenced cells are overwritten or deleted.

• Example: According to the image below, in the referenced cell range, the formula “a17:a29” does not have enough references for the June column so an error occurs.
• How to fix: You just need to expand the referenced cell range from “a17:a29” to “a17:g29“.

Combination error #ERROR

During the use of the function, when the result is an error “#ERROR“, it means that Google Sheets does not understand the formula you entered.

• Example: According to the image below, the formula is missing the semicolon “;“, resulting in the error “#ERROR“.
• How to fix: You just need to add the semicolon “;” to the formula. After the correction, the correct formula is “=ArrayFormula(SUM(VLOOKUP(A4;A17:G29;{2;3;4;5;6;7};FALSE)))”. To avoid this error, please carefully review the formula.

Some notes when combining SUM and Vlookup

Here are some small notes for you when combining the SUM and Vlookup functions:

• If you want the VLOOKUP function to work correctly, you must sort the “column to search” in the search range in “ascending order”.
• Since this is an array formula, after you enter the formula, you need to use the “CTRL + SHIFT + ENTER” key combination to enter the formula and get the most accurate result.

Frequently asked questions when combining SUM and Vlookup

What are the benefits of combining the SUM and Vlookup functions?

Combining the SUM and Vlookup functions will serve a lot in creating reports or processing data. It helps you determine cases of a specific value and then calculate the sum of all related values. In addition, when combined, it makes the process of finding all values in an array much easier and summing related values in a different worksheet.

What functions can Vlookup be combined with?

In Google Sheets, besides combining with the SUM function, the Vlookup function can also be combined with other functions such as QUERY, LEFT/RIGHT, IF, INDEX, AND/OR, SUMIF,…

Conclusion

Above is a detailed guide on how to use the combined SUM and Vlookup functions that FPT Shop introduces to you. Hopefully, with the above information, it will help you work more effectively. If you want to know more useful knowledge, please follow us!

• How to use the TRIM function in Excel, which is extremely simple and you may not know about
• How to calculate percentages in Excel, which is extremely simple and you should know

If you want to own high-quality laptops to handle your work, come to FPT Shop where the products are of good quality and completely genuine.

• Original laptops

You may also like

5 Essential Excel Functions for Beginners

Master the fundamentals of Excel with these 5 basic functions! Learn how to use SUM, AVERAGE, COUNT, MIN, and MAX in order to make calculating and analyzing data easier and more efficient.

Step-by-Step Guide on How to Insert a PDF File into Google Sheets That You Shouldn’t Miss

Discover how to easily and effectively insert PDF files into Google Sheets. Get detailed instructions and tips on leveraging this feature to manage data and share information effortlessly. Read on to learn how to work with PDF files in Google Sheets and enjoy its flexibility and convenience.