How to Use the SUM Function using Query function

In Google Sheets, the SUM function is used to add up a series of numbers in a range of cells. The function can be used in combination with the QUERY function to calculate the sum of specific values returned from a QUERY.

Here's how to use the SUM function with the QUERY function in Google Sheets:

Syntax

=SUM(QUERY(data, query_expression, [headers]))

• data: The range of cells containing the data you want to perform the QUERY on.
• query_expression: A query expression in Google Visualization API Query Language that you'll use to filter the data.
• headers: (Optional) The number of header rows in the data range. If omitted, Google Sheets will try to guess the number of header rows automatically.

Instructions

1. Open your Google Sheet containing the data you want to work with.
2. Click on an empty cell where you want to display the result of the SUM function with the QUERY function.
3. Type the formula, starting with an equal sign (=), followed by the SUM function, and then the QUERY function. For example: =SUM(QUERY(A1:C10, "SELECT B WHERE A = 'X'", 1))
   • Replace A1:C10 with the range of cells containing your data.
   • Replace the query expression "SELECT B WHERE A = 'X'" with the appropriate query to filter your data. In this example, the query will return all values in column B where the corresponding value in column A is "X".
   • Replace 1 with the number of header rows in your data range. If your data has no header rows, you can omit this parameter or set it to 0.
4. Press Enter to execute the formula. The result of the SUM function with the QUERY function will be displayed in the selected cell.

Example

Let's assume we have the following data in our Google Sheet:

Now, we want to calculate the sum of the Amount column for all rows where the Category is "B". Here's how we can use the SUM and QUERY functions together:

1. Click on an empty cell where you want to display the result (e.g., C8).
2. Type the formula: =SUM(QUERY(A1:B6, "SELECT B WHERE A = 'B'", 1))
3. Press Enter to execute the formula.
4. The result (70) will be displayed in cell C8, which is the sum of the Amount column for all rows with Category "B".