You can use four arithmetic operators in Google Sheets QUERY: Addition (+), Subtraction (-), Multiplication (*), and Division (/).

Arithmetic operators perform mathematical calculations in Google Sheets.

You can perform these calculations using regular spreadsheet formulas. However, arithmetic operators become especially useful inside QUERY, where you can combine calculations with filtering, grouping, sorting, and other query clauses.

In Google Sheets QUERY, arithmetic operators can be applied to column identifiers, aggregate functions, numeric constants, and even other arithmetic expressions.

When Are Arithmetic Operators Useful in Google Sheets QUERY?

Use arithmetic operators in QUERY when you want to:

• calculate values directly in the SELECT clause.
• return calculated results without adding helper columns.
• perform calculations on aggregated values such as SUM() or AVG().
• combine calculations with WHERE, GROUP BY, PIVOT, or ORDER BY.

Common Arithmetic Expressions in QUERY

You’ve now seen example formulas that use arithmetic operators in Google Sheets QUERY. We can broadly categorize them into two types:

• Basic row-level arithmetic
• Arithmetic with aggregate functions (SUM, AVG, etc.) and GROUP BY

Basic Row-Level Arithmetic (Multiplication, Addition, Subtraction, and Division)

Using arithmetic operators in the QUERY function is straightforward when performing calculations on values within the same row. Let’s look at a few common examples.

Multiplication in Google Sheets QUERY

In the sample data below, column A contains employee names, column B contains their daily wages, and column C contains the number of days they worked.

To calculate each employee’s monthly pay, multiply the values in columns B and C.

=QUERY(A1:C,"SELECT A, B * C")

Addition in Google Sheets QUERY

The following formula adds the values in columns B and C.

=QUERY(A1:C,"SELECT A, B + C")

For example, if column B contains the basic salary and column C contains the allowance, the formula returns the total salary.

Subtraction in Google Sheets QUERY

In this example, column C contains the total number of days in the month, while column D contains the number of days an employee was absent.

To calculate the number of days worked, subtract column D from column C.

=QUERY(A1:D,"SELECT A, B, C - D")

Division in Google Sheets QUERY

Suppose column B contains the quantity purchased and column C contains the total cost. To calculate the cost per unit, divide the total cost by the quantity.

=QUERY(A1:C,"SELECT A, C / B")

Using Multiple Arithmetic Operators

You aren’t limited to using a single arithmetic operator in a QUERY formula. You can combine multiple operators to perform more complex row-level calculations.

For example, suppose you want to calculate an employee’s gross wages using this formula:

Gross Wage = (Total Days in the Month − Absent Days) × Daily Wage

You can do that with the following QUERY formula:

=QUERY(A1:D,"SELECT A, (C - D) * B")

In this example, the expression (C - D) calculates the number of days worked, and the result is then multiplied by the daily wage in column B.

Note: Arithmetic expressions in QUERY follow the standard order of operations. Multiplication and division are evaluated before addition and subtraction. Use parentheses to override the default precedence.

Arithmetic with Aggregate Functions and GROUP BY

So far, we’ve used arithmetic operators for row-level calculations in the QUERY function. However, QUERY isn’t required for these calculations—you can often use regular spreadsheet formulas instead.

For example, to calculate the cost per unit for each row, you can simply use:

=LET(f, ARRAYFORMULA(IFERROR(C2:C/B2:B)), f)

The real power of arithmetic operators in QUERY comes from combining them with aggregate functions such as SUM, AVG, COUNT, MIN, and MAX, especially when using a GROUP BY clause.

Let’s look at an example.

Suppose you have a dataset containing the date of supply, product name, number of trucks dispatched, and number of rejected truckloads.

To calculate the actual number of trucks shipped for each product, subtract the rejected truckloads (column D) from the dispatched truckloads (column C), and then group the results by product (column B).

The following QUERY formula does exactly that:

=QUERY(A1:D,"SELECT B, SUM(C)-SUM(D) WHERE B IS NOT NULL GROUP BY B")

Now let’s extend this example.

Assume each truck carries 45 cubic meters of material. You can convert the net number of trucks into the total quantity shipped (in cubic meters) by multiplying the result by 45.

=QUERY(A1:D,"SELECT B, (SUM(C)-SUM(D))*45 WHERE B IS NOT NULL GROUP BY B")

Returning Both the Net Truck Count and Quantity

You might expect the following formula to return both the net number of trucks and the equivalent quantity in cubic meters:

=QUERY(A1:D,"SELECT B, SUM(C)-SUM(D), (SUM(C)-SUM(D))*45 WHERE B IS NOT NULL GROUP BY B")

Since the SELECT clause cannot reuse the result of another calculated column, you must repeat the expression or calculate it in an inner QUERY first.

A simple workaround is to use a nested QUERY. The inner QUERY calculates the net truck count, and the outer QUERY converts it to cubic meters.

=QUERY(
  QUERY(A1:D,"SELECT B, SUM(C)-SUM(D) WHERE B IS NOT NULL GROUP BY B"),
  "SELECT Col1, Col2, Col2*45"
)

This returns both the net truck count and the quantity in cubic meters side by side.

How to Rename (Label) Calculated Columns

When you use arithmetic operators in the QUERY function, the calculated columns are assigned default labels. These labels are often long or difficult to read because they’re generated from the expression itself.

You can replace the default label with a more meaningful one by using the LABEL clause.

For example, consider the following formula:

=QUERY(A1:C,"SELECT A, B * C")

The calculated column is assigned a default label similar to:

B * C

(or product(BC), depending on the type of expression and your data).

To replace it with a more descriptive label, such as Monthly Pay, use the LABEL clause:

=QUERY(A1:C,"SELECT A, B * C LABEL B * C 'Monthly Pay'")

The output column will now be labeled Monthly Pay instead of the default expression.

FAQ

Can I use arithmetic operators with SUM() in QUERY?

Yes. You can combine arithmetic operators with aggregate functions such as SUM(), AVG(), COUNT(), MIN(), and MAX(). This is especially useful when using a GROUP BY clause.

Does QUERY support parentheses in arithmetic expressions?

Yes. You can use parentheses to control the order of operations and override the default arithmetic precedence.

Why does my arithmetic QUERY return #N/A?

This usually happens because the SELECT clause cannot reuse the result of another calculated expression. Use a nested QUERY if you need to reference a calculated value in another expression.

Can I rename calculated columns?

Yes. Use the LABEL clause to assign a custom name to a calculated column instead of the default label.

Conclusion

Arithmetic operators let you perform calculations directly within the QUERY function without adding helper columns. While they’re useful for simple row-level calculations, they become especially powerful when combined with aggregate functions and clauses such as GROUP BY, WHERE, and ORDER BY. By using arithmetic expressions together with the LABEL clause and nested QUERY formulas when needed, you can build more compact and flexible queries.