Arithmetic Operators in Query in Google Sheets [How to]

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

As you may know, you can use the arithmetic operators or equivalent Math functions to perform mathematical operations in Google Sheets.

The Query function is not a must for this type of calculation.

But when using the arithmetic operators in Query in Google Sheets, additionally, you can apply conditions and grouping.

In Google Sheets Query, the operands of the arithmetic operators can be the output of appropriate aggregate functions, operators, or constants.

The arithmetic operators return a single numerical value.

The example formulas below can provide you with valuable pieces of information.

With that, it would be easy for you to learn the use of arithmetic operators in Google Sheets Query.

The Use of Four Arithmetic Operators in Query in Google Sheets

I have used all the above-said arithmetic operators in the below Query formulas.

1. Multiplication in Google Sheets Query

My sample data contains the names of a few employees in column A.

Their daily wages are in column B, and the number of days they were present is in column C.

Here I am going to multiply column B by column C to get the monthly payment of employees.

=Query(A1:C,"Select A, B*C")

The use of arithmetic operators in Query is that much simple. Here is another example.

2. Subtraction in Query in Google Sheets

This time column C contains the total days in a calendar month. In column D, I have the absent days of employees.

What I want to do is to calculate the actual present days of employees.

=Query(A1:D,"Select A,B, C-D")

The above Query formula does that.

3. Addition in Google Sheets Query

When you individually use the arithmetic operators in Query, you won’t probably face any issues.

So I am not going into much detail about the formulas above. You may please experiment with that in your free time.

Here is an example Query formula that performs addition.

=Query(A1:C,"Select A,B+C")

In this example, I have used the addition arithmetic operator in Query to add allowances to gross salary.

4. Division in Google Sheets Query

Here we will use the Division arithmetic operator with one of the popular Scalar functions, i.e., DateDiff, in Query.

Formula:

=Query(A1:C,"Select A, datediff(C,B)/30")

I have calculated the total number of days each employee worked from their joining dates to their contract end dates.

For that, I have used the DateDiff scalar function in Query.

Then I divided that output by 30 (days) to get the total number of months each employee worked.

The purpose of this example is to demonstrate to you how to use the division arithmetic operator in Query.

To find the total months from a start date and end date, you can use the Datedif native worksheet function.

=ArrayFormula(datedif(B2:B5,C2:C5,"M"))

Must Check:- Learn Complete Date Related Functions in Google Sheets.

I have detailed the use of all four arithmetic operators in the Google Sheets Query function.

Now, time to give you some additional tips regarding this topic.

Multiple Arithmetic Operators in Google Sheets Query

As usual, here you are going to get some additional tips.

No doubt, the below tips are in line with the main topic. Please take a look at the below screen capture.

Multiple Arithmetic Operators in Query Formula

Generic Formula:

Gross Wage = (Total Days in the Month - Absent)*Daily Wages

i.e,

Gross Wage = (Column C - Column D)*Column B

Query Formula.

=Query(A1:D,"Select A, (C-D)*B")

I hope it’s self-explanatory.

Grouping and Mathematical Calculations in Query in Sheets

As I mentioned at the beginning, the operands of the arithmetic operators can be the output of aggregate functions in Google Sheets Query.

Here I am using the aggregate function SUM as the operand.

When you use Aggregate functions in Google Sheets Query, no doubt the grouping may involve.

Here is one Query formula example that will demonstrate how to use arithmetic operators in Query with aggregation functions for grouping.

Formula Example to Grouping and Mathematical Calculations in Query

The following sample data shows the supply status of some items.

Sum and Arithmetic Operators in Google Sheets Query

The quantity supplied is in numbers (number of trucks) in column C.

In column D, you can see the rejected truckloads due to the poor quality of the material.

I want to subtract column D from column C to get the actual quantity supplied and group column B, i.e., products.

The Query formula in cell F1 does that.

=query(A1:D,"Select B, Sum(C)-Sum(D) where B is not null Group by B")

I am taking this example further to make you understand the use of multiple arithmetic operators in Google Sheets Query.

Assume one truckload of material is equal to 45 cubic meters.

Then I can convert the above number of trucks to quantity in cubic meters.

=query(A1:D,"Select B, (Sum(C)-Sum(D))*45 where B is not null Group by B")

When using multiple arithmetic operators in Google Sheets Query, the formula may sometimes return the N/A error.

In the above example, you can’t show both numbers of trucks and quantity in cubic meters together.

Here is my attempt that would return an error.

=query(A1:D,"Select B, Sum(C)-Sum(D), (Sum(C)-Sum(D))*45 where B is not null Group by B")

Here is the correct use of multiple arithmetic operators in Query. Actually, it’s a Query formula combination.

=Query(query(A1:D,"Select B, Sum(C)-Sum(D) where B is not null Group by B"),"Select Col1,Col2, Col2*45")

I hope I have covered everything you want to learn about using arithmetic operators in Query in Google Sheets.

Any doubt, please post in the comments.

14 COMMENTS

Chan February 7, 2021 At 5:45 am

Hi Prashant

Thank you so much for sharing this. It’s immensely helpful articles.

Question: Whats the power operator (exponent operator) inside the query? I tried using “^”, “**” but neither works? Please suggest
=Query({A1:A50}, "select Col1^2")

- Prashanth February 7, 2021 At 10:00 am
  
  Hi, Chan,
  
  As far as I know, such use is not supported in Query. So try to use it as below.
  
  =ArrayFormula(Query(if(len(A2:A),A2:A^2,), "select Col1"))
  
  I have started the range from A2 instead of A1 considering the title in A1.
  
Pradumn Asawa July 22, 2020 At 11:27 am

Can I use round in google sheets query? if Yes how?
Like if a 3/10 it gives me 0 instead of 0.3
and 12/10 it gives me 1 instead of 1.2
and 23/10 it gives me 2 instead of 2.3

How can this be done in google sheets query?

Dimitri May 24, 2020 At 10:54 pm

Hi Prashanth,

I hope you’re doing great. Infinite thanks for all the different articles you’ve done so far. It has helped me a couple of times!

I’ve got a question about query multiplication and I’m not sure there is an easy fix to that…

You’ve 2 columns with numbers and some raws from 1 column have empty cells. If I do the Query, there is going to be empty raws where there are only 1 number (it looks like that Google Sheets Query doesn’t multiply a cell with an empty cell).

Any thoughts about that would be very appreciated 🙂

Thanks, & take care!

Dimitri

- Prashanth May 25, 2020 At 8:57 am
  
  Hi, Dimitri,
  
  Assume the columns are A and B, and A1:1 (first row) contains headers.
  
  Then you may try it.
  
  =query(query(A2:B,"Select * where A*B is not null",0),"Select Col1,Col2,Col1*Col2",0)
  
  It would return the result in three columns – Colum A values, Column B values, and the multiplication result. There won’t be any blank row in the output.
  
  - Dimitri Leroux May 25, 2020 At 11:23 pm
    
    Hey Prashanth 🙂
    
    Thanks for this quick answer! I’m very pleased to read from you. One thing I noticed when I saw your solution is that I forgot to mention my need to keep those numbers that have empty cells in front of (like if they were multiplied by 1). Visual explanation : (desired result in column C)
    
    A|B|C
    2|14|28
    3|7|21
    4|4
    
    So far, and after reading your blog I’m using the N() function. That looks like this (let’s consider that empty cells appear only in column B):
    
    =Query( {Query({A:B},"Select Col1",1),ArrayFormula(N(Query({A:B},"Select Col2",1))+1)},"Select Col1*Col2",1)
    
    Is there any easiest way to find the same solution?
    
    Thanks again!
    
    Best
    
    - Dimitri Leroux May 25, 2020 At 11:29 pm
      
      Edit: I can see for the visual explanation that spaces between letters have been cropped. On the 4th line, the second number “4” belongs to column C
      🙂
      
      - Prashanth May 26, 2020 At 11:19 am
        
        Hi, Dimitri Leroux,
        
        Now I could understand what you are trying to do.
        
        Here are my formulas. Row#1, i.e. A1:B1, contains headers. So I am not considering it in my formula. I would select A2:B.
        
        Formula without using Query but using arithmetic and comparison operators in cell C1:
        
        ={"Product";ArrayFormula(if(A2:A+B2:B>0,if(A2:A="",1,A2:A)*if(B2:B="",1,B2:B),))}
        
        Or you can use the below Query in cell C1:
        
        =Query(ArrayFormula(if(A2:A+B2:B>0,{if(A2:A="",1,A2:A),if(B2:B="",1,B2:B)},)),"Select Col1*Col2")
        
        From my test your formula is not working properly.
        
Alex May 5, 2020 At 12:50 am

Hey Prashanth,

Thanks so much for all of this! I never even knew about the Query!

I’m having a little trouble with combining things from different tabs and I’m not sure what I’m doing wrong. I’d be happy to attach screen shots if you want to e-mail me, but basically:

Row 1 is the header

Column A contains names, some of which are repeating ex:
Tom
Larry
Cindy
Tom
Mary
Cindy
Tom

Columns B-H are different stats for each person, such as wins, loss, ties, etc. but from different seasons.

So I’m basically trying to consolidate to only have 1 of each name, and add their stats so each name has 1 row with all of their stats across B-H, hoping this would enable me to rank based on total numbers.

I was taking all of the information from tab 2 and trying to consolidate on tab 1, but I can’t seem to figure out what the formula is supposed to be for everything. Hope this all makes sense, and thanks in advance.

- Prashanth May 5, 2020 At 9:49 am
  
  Hi, Alex,
  
  If your data is in ‘Sheet1’, in Sheet2 (or any other sheet) use this formula.
  
  =query(Sheet1!A1:H,"Select A, sum(B),sum(C),sum(D),sum(E),sum(F),sum(G),sum(H) where A is not null group by A",1)
  
  If it doesn’t work try changing the first and last comma to semicolon as per regioinal settings.
  
  Also, you can share with me a demo sheet.
  
Varun July 1, 2019 At 3:42 pm

Hi,

My query is outputting a column with dates. Now I want to reduce each date by one day in the same column. For example, if the date is 06/29/2019, I want it to show 06/28/2019 using mathematical operators within the query function. Any ideas?

- Prashanth July 1, 2019 At 8:30 pm
  
  Hi, Varun,
  
  See a similar topic: How to Add or Subtract N Days to The Dates in a Column in Sheets Query.
  
  Best,
  
Cory February 26, 2019 At 9:20 am

What if you wanted to query another sheet but add a calculation using data from the current sheet?

- Prashanth February 26, 2019 At 6:47 pm
  
  Hi, Cory,
  
  Here is one example.
  
  =Query({A1:B,Sheet1!C1:C},"Select Col1, Col2*Col3")