Aggregation Function in Google Sheets Query: Sum, Avg, Count, Max, Min

There are aggregation functions equivalent to Sum, Average, Count, Max, and Min in Google Sheets Query. What are they?

They are sum(), avg(), count(), max(), and min().

One must know how to use aggregation in Google Sheets Query to manipulate data properly.

The Query function is easy to learn if you know how to use aggregation functions.

As mentioned above, there are five aggregation functions in Google Sheets Query for data manipulation.

I have included several Query formula examples in this tutorial to help you learn their usage.

Sample Data:

Learn All Aggregation Functions in Google Sheets Query

I already have 40+ Query-based tutorials on this blog. Many of them are for advanced users.

I have missed writing some basic Google Sheets tutorials on the Query function.

So this time, you can learn the available aggregation functions in Query, which are already part of my many Query tutorials.

1. How to Use the SUM Function in Google Sheets Query?

All the examples below on using the aggregation functions Sum, Avg, Count, Max, and Min in Google Sheets Query are based on the sample data above (please refer to the screenshot).

Just try to learn one of them. The usage of all the functions is the same.

I am starting with the sum() function. So learn it thoroughly to conquer the rest without any additional effort.

sum() Multiple Columns

How to sum multiple columns using Query in Google Sheets?

This answers.

=query(A4:F,"Select Sum(C),Sum(D),Sum(E),Sum(F)",1)

Sum Multiple Columns Using Query in Google Sheets

This formula sums all four numeric columns.

sum() Single Column

Use the following formula if you want to sum only a single column using Query.

=query(A4:F,"Select Sum(C)",1)

Conditional sum() Single Column

Suppose you want the formula to Sum column F if column B is “A.” It’s called conditional sum.

Based on the above sample data, here is the formula to conditionally sum a single column using Query in Google Sheets.

=query(A4:F,"Select Sum(C) where B='C'",1)

Without Query, to conditionally sum a single column, you can use the function SUMIF.

E.g.:

=sumif(B4:B,"C",C4:C)

Please note that the SUMIF is not case-sensitive.

Multiple Column Conditional sum()

Example:

=query(A4:F,"Select Sum(C),Sum(D),Sum(E),Sum(F) where B='C'",1)

Multiple Column Group and Sum()

Please take a closer look at the example below. It shows my sample data on the left-hand side and the grouped sum on the right-hand side.

=query(A4:F,"Select B, Sum(C),Sum(D),Sum(E),Sum(F) where A is not null group by B",1)

I have grouped column B and summed all the columns that contain numbers.

In earlier formulas, there were no columns in the Select clause. This time there is column B as “Select Column B” and, of course, the Group clause at the end part.

The Where clause contains “A is not null” and skips blank rows in the result.

In short, the sum() function in Query can replace the native worksheet functions SUM, SUMIF, or SUMIFS.

2. How to Use the AVG Function in Google Sheets Query?

I have detailed above how to use the Sum aggregation function in Google Sheets Query.

You can use the Avg aggregation function in line with that. Just replace Sum with Avg.

If you ask me how to find the average using Query, here are the examples.

avg() in a Single Column

How to find the average of the numbers in column C using Query?

=query(A4:F,"Select Avg(C)",1)

It is equivalent to the AVERAGE aggregation function.

avg() in Multiple Columns

Problem:- Find the average of columns C, D, E, and F using Query.

=query(A4:F,"Select Avg(C),Avg(D),Avg(E),Avg(F)",1)

Conditional avg() Single Column

We can replace the following avg() aggregation function usage in Query with the AVERAGEIF worksheet function.

=query(A4:F,"Select Avg(C) where B='C'",1)

E.g.:

=averageif(B4:B,"C",C4:C)

Please don’t forget about the case sensitivity factor.

Conditional avg() Multiple Columns

The following code returns the conditional average of four columns.

=query(A4:F,"Select Avg(C),Avg(D),Avg(E),Avg(F) where B='C'",1)

Multiple Column Group and Avg()

This example demonstrates why the Query is a must to find an average in different ways.

=query(A4:F,"Select B, Avg(C),Avg(D),Avg(E),Avg(F) where A is not null group by B",1)

3. How to Use the COUNT Function in Google Sheets Query?

Similar to the Sum() and Avg() aggregation functions, you can use the Count() function too in Query. See the different Count formulas.

Count() a Single Column

Formula:

=query(A4:F,"Select Count(C)",1)

count() Multiple Columns

The below Query aggregation formula counts four columns separately at a time.

=query(A4:F,"Select Count(C),Count(D),Count(E),Count(F)",1)

Conditional count() a Single Column

We can replace this Query aggregation formula with COUNTIF also.

=query(A4:F,"Select Count(C) where B='C'",1)

COUNTIF: =countif(B4:B,"C")

Conditional count() Multiple Columns

The following code returns the conditional count of four columns.

=query(A4:F,"Select Count(C),Count(D),Count(E),Count(F) where B='C'",1)

Multiple Column Group and count()

This is unique to Query only.

=query(A4:F,"Select B, Count(C),Count(D),Count(E),Count(F) where A is not null group by B",1)

See the illustration below. All the above-count formulas are included in this.

4. How to Use the MIN Function in Google Sheets Query?

I have shared five formula variations with the Count Aggregation function in Query in Google Sheets.

You can use all those variations with min() too.

See the formula examples of how to use the Min aggregation function in Google Sheets Query.

min() a Single Column

Have you ever used the MIN function to return the minimum value in a column in Google Sheets? This one is similar to that.

=query(A4:F,"Select Min(C)",1)

min() Multiple Columns

Query Formula:

=query(A4:F,"Select Min(C),Min(D),Min(E),Min(F)",1)

Single Column Conditional min()

We can use the Min aggregation function in Query or the MINIFS worksheet function to calculate the min in a column based on a condition.

=query(A4:F,"Select Min(C) where B='C'",1)

MINIFS: =minifs(C4:C,B4:B,"C")

Multiple Columns Conditional min()

It conditionally returns the minimum value in columns C, D, E, and F.

=query(A4:F,"Select Min(C),Min(D),Min(E),Min(F) where B='C'",1)

Group and min()

I find the Query quite handy for this type of min calculation,

Of course, we can now use the new Lambda helper functions with MINIFS to get similar results.

=query(A4:F,"Select B, Min(C),Min(D),Min(E),Min(F) where A is not null group by B",1)

See all the above formulas in this screenshot.

5. How to Use the MAX Function in Google Sheets Query

Finally, here are the formula examples of using the Max() aggregation function in Google Sheets Query.

How to find the max() value in a Single Column?

Formula:

=query(A4:F,"Select Max(C)",1)

You may please check the MAX function also.

Finding the max() value in Multiple Columns

Formula:

=query(A4:F,"Select Max(C),Max(D),Max(E),Max(F)",1)

Single Column max() with Conditions

Query Formula:

=query(A4:F,"Select Max(C) where B='C'",1)

The following MAXIFS will be the perfect alternative to this aggregation function in Google Sheets Query.

=maxifs(C4:C,B4:B,"C")

max() in Multiple Columns with Conditions

=query(A4:F,"Select Max(C),Max(D),Max(E),Max(F) where B='C'",1)

Group and max()

A formula that is unique to Query.

=query(A4:F,"Select B, Max(C),Max(D),Max(E),Max(F) where A is not null group by B",1)

That’s all. Enjoy!

24 COMMENTS

ali January 10, 2024 At 3:57 pm

Good day, I have a problem with the GOOGLEFINANCE command, and I came across your post while trying to solve it. I would be happy if you could help me.

In the spreadsheet, I am attempting to find the lowest price of stock market shares between a certain date:

=MIN(GOOGLEFINANCE(A4, "LOW", "01.09.2023", "29.12.2023"))

This formula works and gives the correct result.

However, when I try to find the highest price using:

=MAX(GOOGLEFINANCE(A4, "LOW", "01.09.2023", "29.12.2023"))

It returns a value like this: 45135.75694. I would appreciate it if you could assist me with this issue.

- Prashanth KV January 10, 2024 At 6:55 pm
  
  Hi Ali,
  
  Firstly, I recommend replacing “01.09.2023” with DATE(2023, 9, 1) and “29.12.2023” with DATE(2023, 12, 29) to avoid date formatting-related issues. However, these changes won’t address the problem with MAX.
  
  The value you see when using MAX is a timestamp in date value.
  
  You can use the following formula:
  
  =QUERY(GOOGLEFINANCE(A4,"LOW", DATE(2023, 9, 1), DATE(2023, 12, 29)), "select max(Col2) label max(Col2)''", 1)
  
  I hope that helps.
  
  - ali January 16, 2024 At 9:15 am
    
    When I enter the query into the spreadsheet, it returns an error due to a formula parsing issue.
    
    - Prashanth KV January 16, 2024 At 11:30 am
      
      Please feel free to share a copy of your sheet with the original data replaced by sample data.
      
      - ali January 19, 2024 At 8:30 am
        
        I have emailed the file to your email address.
        
        
        Prashanth KV January 19, 2024 At 9:08 am
        
        Already replied. The issue is with the function separator. You must use a semicolon “;” instead of a comma “,” in your formulas. I couldn’t edit your sheet as it was set to VIEW only.
        
Jason July 25, 2021 At 1:55 am

Hi,

Thank you for this great article!

Does the Query command support Countif in the select statement? I keep getting VALUE errors when trying to use it.

Let’s say I have a three-column table with StudentName, Division, and ActiveFlag where the ActiveFlag is either “Active” or “Inactive.”

I want the query results to have three output columns for Division, ActiveStudents, InactiveStudents, and total the number of students from each division for their respective status flags.

example input:
Name, Division, StatusFlag
Bob, A, Active
Joe, A, Inactive
Ann, B, Active
Sara, B, Inactive
John, A, Active

Example Output:
Division, ActiveStudents, InactiveStudents
A, 2, 1
B, 1, 1

I hope this makes sense. Thanks again for the article! Any help is greatly appreciated.

Cheers!

- Prashanth July 25, 2021 At 10:56 am
  
  Hi, Jason,
  
  Assume the data is in A2:C, and the first row takes the field labels (header row).
  If so, in cell E1, insert the below Query formula, which uses the Pivot clause.
  
  =query(A1:C,"Select B,count(A) where A is not null group by B pivot C",1)
  
  Related:- What is the Correct Clause Order in Google Sheets Query?
  
Thomas March 15, 2021 At 6:16 am

Hi,

I’m looking to do something similar to Ali except I am attempting to get a count instead of a sum. I switched around the suggestion you left:

=iferror(n(query(Energy_Efficiency!D2:F, "Select Count(D) where D='State' and F='Action Plan' label Count(D) ''")),0)

However, I’m now attempting to count all instances of a keyword, along the lines of something similar to

=Countif(Energy_Efficiency!I1:I, "*Action Plan*")

I’m running into the issue where Countif doesn’t allow (?) for more than arguments but the former iferror(n(query formula runs into an error using * and only returns 0.

Do you know of any workarounds? Sorry if this is unclear!

- Prashanth March 15, 2021 At 12:55 pm
  
  Hi, Thomas,
  
  You can use wildcards in COUNTIFS too.
  
  Eg.
  
  =COUNTIFS(Energy_Efficiency!D1:D,"State",Energy_Efficiency!F1:F, "*Action Plan*")
  
  If you are particular in using QUERY, you should use the CONTAINS substring match as below.
  
  =iferror(n(query(Energy_Efficiency!D2:F, "Select Count(D) where D='State' and F contains 'Action Plan' label Count(D) ''")),0)
  
Mike February 23, 2021 At 6:40 pm

Every time I add the AND SUM(G)>0 I get an error that the sum(g) can’t be part of a where clause. Col G contains both positive and negative numbers and I want to know the balance only if it’s positive. I hope my question makes sense?

- Prashanth February 24, 2021 At 10:16 am
  
  Hi, Mike,
  
  In a blank cell (eg. cell A1) get the total of column G. Then do a logical test as below.
  
  =if(A1<=0,,your_query_formula)
  
  If this doesn't help, if possible, reply with your (sample) sheet link.
  
Andrew Edwards September 23, 2020 At 4:44 am

Hi,

Love this article.

I’m trying to select the value (text) in column A based in the max value (numeric) in column B, there may be multiple max values and so I want to return each of them as a list.

I’m sure query is the way to do it and that the max() aggregation needs to be there but I can’t make it work.

I’ve tried;

Formula # 1

=QUERY(A1:B10,"select A where max(B)")

Formula # 2

=QUERY (A1:B10,"select A, max(B)")

and a couple of others besides. Any tips on getting this query to work.

Many thanks.

- Prashanth September 23, 2020 At 11:24 am
  
  Hi, Andrew Edwards,
  
  You can simply use the Filter function.
  
  =filter(A1:A10,B1:B10=max(B1:B10))
  
  If you want Query, use the MAX worksheet function within Query as below.
  
  =query(A1:B10,"Select A where B="&max(B1:B10))
  
Rob Weber May 22, 2020 At 8:50 pm

Hi there, I’m hoping someone can help me out.

I have a spreadsheet template from my office that I’d like to convert from excel to Google sheets so that I can have some data added automatically from a parsed source.

I have set this up, however, some of the formulas used in the original excel file are not able to be used in the sheets. Specifically I have a formula using aggregate functions. I cannot seem to figure out how to get this formula right in sheets.

This is the formula: =IFERROR(IF(AGGREGATE(3,5,[@[OUTSTANDING AMOUNT]])=1,1,0),"")

I need to rewrite this…I am assuming as a Query? Any assistance is greatly appreciated.

P.S. The @ aspect of the formula was added automatically by the new version of excel. I cannot seem to get it to show the original formula I had in that cell prior.

- Prashanth May 23, 2020 At 8:21 am
  
  Hi, Rob Weber,
  
  The Aggregate alternative is Subtotal, which is available in Google Sheets.
  
  If you can link to an example sheet, that contain your said problem and the expected output, I can try with Subtotal or Query.
  
Producer February 25, 2020 At 10:44 pm

Hello! I’m trying to create a query that grabs the hours worked from a separate sheet and then averages them to check for the average variance between hours estimated and actuals.

=SUM(QUERY(Test!$D:$K,"select K where D MATCHES '"&$B3&"'",0))

This allows me to total the actual hours worked that week.

=Average(QUERY(Test!$D:$J,"select J where D MATCHES '"&$B3&"'",0))

However, this gives me a DIV/0 error.

Any advice?

B3 is the name of the engineer who worked on the task. That name appears on both charts and I’m trying to do it this way so I don’t have to manually add tasks completed by the engineers.

- Prashanth February 26, 2020 At 9:10 am
  
  In the second Query did you use the column in the select clause correctly? I mean “select J” instead of “select K”?
  
  If that is not making the error, then I wish to see a demo of your dataset to suggest the cause or answer the problem.
  
ShirlyGirl February 21, 2020 At 5:14 pm

I have multiple boolean columns in my sheet. I’m trying to get a count of the number of times ‘true’ appears for each of the columns using a query (I was able to achieve it using COUNTIF formula directly in the sheet). Will any of these math functions help me achieve this via query?

- Prashanth February 21, 2020 At 7:35 pm
  
  Hi, ShirlyGirl,
  
  Multiply the range by 1 to convert TRUE to 1 and FALSE to 0. Then use the Query. Here is an example.
  
  =ArrayFormula(query(A1:C*1,"Select Sum(Col1),Sum(Col2),Sum(Col3)"))
  
Emerson Luiz Oliveira July 26, 2019 At 9:08 pm

Thanks a lot for the article!

One doubt: how can I eliminate the header that generates automatically? In the sum example, how can I display the result without showing Sum Math?

Once again, thanks a lot!

- Prashanth July 27, 2019 At 9:57 am
  
  Hi, Emerson,
  
  Use the Query Label clause. Here is one example to the use of Label caluse in Sum.
  
  =query(A4:F,"Select Sum(C),Sum(D),Sum(E),Sum(F) label Sum(C)'', Sum(D)'', Sum(E)'', Sum(F)''",1)
  
  To modify the label, I mean the sum Math, use this.
  
  =query(A4:F,"Select Sum(C),Sum(D),Sum(E),Sum(F) label Sum(C)'Maths', Sum(D)'Chemistry', Sum(E)'Physics', Sum(F)'Total'",1)
  
  Best,
  
Ali July 14, 2019 At 4:11 pm

Thanks a lot! Great article.

Have a question:

I’ve written a simple query to add up the sales we got from each lead, where the lead source is Facebook.
leads = a named range
AD = sale amount
L = lead source column

query(leads, " select sum(AD) where L = 'Facebook' label sum(AD) ''")

1. if there is at least 1 Facebook lead that has a sale, work correctly (eg. $3,000)
2. if there is at least 1 Facebook lead, but none of them had a sale, give #N/A
3. if there are no Facebook leads at all, then it returns empty cell

I want scenarios 2 & 3 to default to 0, but can’t figure it out with IFNA or IFERROR or anything else I’ve tried. Could you please help me out?!

Thank you so much <3

- Prashanth July 14, 2019 At 4:57 pm
  
  Hi, Ali,
  
  Try this Query. In this, the function N converts the blank to zero. The IFERROR converts the error value to zero.
  
  =iferror(n(query(leads, "select sum(AD) where L = 'Facebook' label sum(AD)''",1)),0)