Technology

How to Combine QUERY and SUM in Google Sheets: Simple and Effective Guide

Combining QUERY and SUM in Google Sheets allows you to quickly perform calculations. There are certain problems that cannot be solved using a single function, so it requires combining multiple functions. This article will guide you on how to combine QUERY and SUM!

Administrator

13 February, 2024

274

QUERY and SUM are two popular functions, used frequently in problem-solving. In complex scenarios, QUERY and SUM are often combined to solve problems quickly and effectively. In today’s article, let’s explore how to combine these two functions at FPT Shop.

Using QUERY and SUM functions in Google Sheets

QUERY is a function that allows you to use database commands (SQL – structured query language, a code used to communicate with databases) to manipulate data (you can query data, filter data, or combine multiple data from sheets into one sheet, …) easily and flexibly. The formula of the function is: =QUERY(data; query; headers), wherein data is the range of data to be calculated, query is the query statement to output the desired data, and headers can be left blank or filled with a value.

SUM is a well-known and useful function, as well as a basic function. The SUM function is used to calculate the sum with parameters that can be single numbers or ranges of cells. The formula of the function is: =SUM(Number1, Number2, …), where number1, 2, … are the numerical values.

Combining QUERY and SUM in Google Sheets

In order to handle more complex problems, we cannot use these two functions separately, and must combine QUERY and SUM. You can use the SUM function nested in the QUERY function with the formula: =QUERY(data;”SELECT SUM!Group by”), where data is the range of data, SELECT SUM is the desired value to be calculated, and Group by is the value that defines the group.

In addition, you can also combine QUERY and SUM by using the SUM function nested in the QUERY function with the formula: =SUM(QUERY(data;query)). In general, this combination is quite complex, and you need to practice to become proficient.

Common errors when combining QUERY and SUM

When combining QUERY and SUM, we often encounter errors, each error originating from different causes and requiring different remedies. Some common errors include:

Error #N/A

Error #N/A occurs when the formula you use contains content that is not available in the data range, resulting in the formula being unable to calculate. The cause of this error is when using search functions, reference functions, and the object to search or reference is not available in the data range, resulting in an #N/A error.

Error #N/A is not an error of the function or the formula, but rather the inability to find the desired object. Therefore, in many cases, we only need to hide this error and consider cases where the data does not exist before performing the functions.

Error #REF

Error #REF! occurs when the formula refers to an invalid cell. This often happens when the cells referenced by the formula are overwritten or deleted. You just need to review your command and ensure that it has the correct syntax.

Error #VALUE

Error #VALUE! means: “An error has occurred with the formula you just entered.” This error is very general, making it difficult to find the exact cause. You can review your data and the calculation formula to see if they are entered correctly.

Examples of combining QUERY and SUM

You need to query data extracted from a table to get the total term of each Branch in the table. The formula will be: =SUM(QUERY(‘Sheet1’!A1:I36;”select D, Sum(G) Group by D label sum(G) ‘Total term’”)). Where ‘Sheet1’!A2:I36 is the value of the data range to be queried, SUM(G) represents calculating the sum of column G, Group by D groups the values selected according to column D, and label sum(G) ‘Total term’ is the header for the total value.

Notes when combining QUERY and SUM

When combining QUERY and SUM, you need to pay attention to parentheses, quotation marks, double quotes, and single quotes. If not used carefully, these can cause function errors. When querying data, be mindful of spaces, as the function may produce errors if written closely together. Text-based data in queries must be enclosed in single quotes.

The main function of QUERY is to query and return data as requested. However, where clauses cannot express multiple conditions at the same time or conditions that need to be calculated. Therefore, selecting the SUM function nested in the WHERE clause will help solve this problem. QUERY has the ability to query and reference data, and can also be combined with other functions, conditional statements, comparison operators, AND/OR, IF, and more.

Conclusion

In conclusion, through this article, we have gained a better understanding of how to combine QUERY and SUM functions. The usage may not be easy but not overly complex either. You need to practice several times to become proficient in combining these two functions, as they will greatly assist you in calculating and analyzing complex problems.