Group and Average Unique Column in Google Sheets

The title of this post may be a little bit confusing if you are not familiar with the grouping of data. So first let me explain what I am trying to say with the above title Group and Average Unique Column in Google Sheets. Let me begin with Average, the main topic of this post.

Calculate Average in Google Sheets:

As you may know, when we talk about spreadsheets, the Average is the Sum of a column divided by the count of the same column or another column with the same array size in the data set.

While calculating the average, we are not taking into consideration whether there are multiple occurrences of the same value in our data set.

Calculate Average of Unique Column in Google Sheets:

If the average is the sum divided by count, unique column average is the sum divided by Count unique.

You May Like: Learn Google Sheets all Count related functions

Let me make it clearer to you.

example to average and unique average in google sheets

The total count of records in this data set is 7 and the sum of column C is 80. So the normal average is;

80/7 = 11.43

In Google Sheets, there is no need to count and sum to find an average. You can calculate the average using the Average formula in Google Sheets.

=average(C2:C8)

There is no average unique in Google Sheets. So to find average unique, first, you need to find the count of unique columns as below.

=countunique(A2:B8)

Must Check: How to Use All Google Sheets Count Functions [All 8 Count Functions]

This would return 5 as there are only five unique records in this data set. Because I’ve considered columns A and B. If I only choose column A, then there would be only 2 unique values.

We already know the sum of column C is 80. You can find the average of unique columns as below.

=sum(C2:C8)/countunique(A2:B8)

It would return the value 16 as average as it’s equal to 80/5. Now let’s see what is group wise average in Google Sheets.

How to Group and Average Unique Column in Google Sheets

Just like the above example, in grouping also there are two types of averages. Normal group wise average and average based on unique column values. For both these, we can use Query function as it’s the best in the grouping of data.

Similar: How to Group a Column Based on First Few Characters in Google Sheets

Calculate Normal Group Wise Average in Google Sheets

Result of Normal Group Wise Average

It’s like first sum column C if the value in Column A is “Cary”. If you sum so, you will get the total value as 50.

Divide this value by the total occurrences of the name “Cary” which is 4. So50/4 = 12.5.

That’s the output you can see on the above image. Just follow this steps for the other person too.

But you can use a Query formula as below to automate the grouping of average in Google Sheets.

The Formula to Calculate the Normal Group Wise Average

This Query formula uses the Avg Aggregate function in Query.

=query({A2:A,C2:C},"Select Col1, Avg (Col2) where Col1<>'' group by Col1 label Avg(Col2)''")

Here Col1 is Column A and Col2 is Column C as I have skipped column B in the Query formula range.

The reason, we have nothing to do with unique values here as it’s a normal group wise average. We only require Column A and C.

To understand the above formula you may first understand how to use Query in Google Sheets.

Tips: Learn Google Sheets Query

I have used the group clause and Avg aggregate function in Query to get the group-wise average.

Then how to Group and Average Unique Column in Google Sheets?

Calculate Unique Group Wise Average in Google Sheets

This Query formula uses the Sum and Count Aggregate functions in Query.

For this, first, you should make a group-wise sum and then a unique group wise count.

That means there are two groupings (formulas) involved. So we can use two query formulas as below and divide the outputs.

Formula 1:

This formula is similar to the above average formula. Here I’ve used the aggregate function SUM, not AVG.

=query(ArrayFormula({A2:A,C2:C}),"Select Col1, Sum (Col2) where Col1<>'' group by Col1 label Sum(Col2)''")

See the result.

Group wise sum to find average

Now we want to do a group-wise unique count.

=query(unique(A2:B),"Select Col1, Count (Col1) where Col1<>'' group by Col1 label Count(Col1) ''")

This formula is also similar to the above one. But here instead of Sum, I’ve used the Count aggregate function in Query.

Additionally, you can see the use of Unique in the Query range. Also, the Query range is different. I’ve included the column A and B here to find unique values.

count unique using Query

Now you only need to divide these values from the above corresponding summed values.

We can’t do this until we remove the names associated with the values. You can remove that easily. How?

Just remove theCol1, in the “Select” clauses of both the above formulas. Then you will only be left with summed values in one formula and the count unique values in another.

Now you can divide these values and the final formula would be like this.

=ArrayFormula(query({A2:A,C2:C},"Select Sum (Col2) where Col1<>'' group by Col1 label Sum(Col2)''")/query(unique(A2:B),"Select Count (Col1) where Col1<>'' group by Col1 label Count(Col1) ''"))

Additionally, I have used the array formula as we used the division operator (forward slash) between the two query formulas.

Here is the group-wise unique column average result.

group wise unique column average result

We got the unique column average. But does this justify my post title saying Group and Average Unique Column in Google Sheets? Certainly not! To complete the above process, you should place the names before the output column. That makes the grouping perfect.

Just add another piece of formula in front of the above formula to insert the unique names before the output values.

Compare the below formula with the just above formula. So you can understand what I have done to get the name in front of the average.

={unique(sort(A2:A8)),ArrayFormula(query({A2:A,C2:C},"Select Sum (Col2) where Col1<>'' group by Col1 label Sum(Col2)''")/query(unique(A2:B),"Select Count (Col1) where Col1<>'' group by Col1 label Count(Col1) ''"))}

See the final result.

two column unique average in google sheets

Conclusion

To Group and Average Unique Column in Google Sheets, you should know the basic use of Query function. Otherwise, you may not be able to understand how the formula develops.

There are ample Query related tutorials on this page. Find time to try few of them.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.