I want to find the average of values only in the visible rows in Google Sheets. How to do that? I mean, how to exclude other rows?

You can learn here in this tutorial how to find the average of visible rows in Google Sheets.

There are three popular Average functions in Google Sheets. They are Average, Averageif, and Averageifs.

None of them support the above type of average calculation without workarounds.

In addition to the above, you can use the Query function also to calculate the average in Google Sheets.

Here in Query also, you should follow some workarounds to exclude hidden rows in the average calculation.

You can learn those workarounds in this Google Sheets average calculation tutorial.

Note:- Learn the above-said functions from my Google Sheets functions guide.

In this Google Sheets Tutorial, you will get a few examples of using the above functions in filtered data.

I know I shouldn’t limit this tutorial to filtered data because Google Sheets now has a new feature to hide rows by Row and Column Grouping.

Must Read: How to Group Rows and Columns in Google Sheets

With the grouping of rows, users can easily hide and unhide groups of rows in Google Doc Sheets.

So you should also know how to exclude the rows hidden by row grouping in Average, Averageif, Averagifs, and Query functions.

In addition to the above, you can manually hide the rows.

All these aspects we will discuss in this tutorial about calculating the average of visible rows.

In short, in this Google Sheets tutorial, you can learn the following things.

1. How to calculate the average in Google Sheets excluding filtered out | hidden via Row grouping | manually hidden rows (unconditional average).
2. Single conditional average in Google Sheets excluding filtered out | hidden via Row grouping | manually hidden rows.
3. Multiple conditional averages in Google Sheets excluding filtered out | hidden via Row grouping | manually hidden rows.
4. Query function to calculate average in Google Sheets – Excluding Filtered | Hidden | Grouped Rows.

Let’s begin.

Average of Visible Rows

Average Excluding Filtered or Hidden Rows (Unconditional Average)

What is average and how to calculate it? First, you should know this. Then only you can understand the rest of this tutorial.

Average = total/count.

In column A, you can find the names of a few persons and their ages in column B. The total is 156.

=B2+B3+B4+B5+B6+B7

or

=sum(B2:B7)

The count is 6.

=count(B2:B7)

So you can calculate the average as below.

=sum(B2:B7)/count(B2:B7)

We can instead use a dedicated worksheet function.

=average(B2:B7)

The result would be 26.

Here is a new data set.

I want to filter out the gender “M” and do the average of the rest. Let’s see how to do that.

Select the range A1:C7 and apply Data > Create a filter.

Now I can click on the field name gender in cell B1 and uncheck “M.”

See the average formula in cell C9. The answer is not correct because it includes the values in hidden rows.

Then what should be the answer?

=(30+24+31+20)/4

The answer should be 26.25.

If you want to exclude the values in the filtered-out rows, you should find an alternative formula to Average().

There is one formula for this using the Subtotal function.

Average of Visible Rows in Google Sheets Using the Function Subtotal and Function # 1

When you want to find the average in Google Sheets excluding filtered rows, you can use the Subtotal function and function number 1 as below.

=subtotal(1,C2:C7)

Average of Visible Rows in Google Sheets Using the Function Subtotal and Function # 101

But remember one thing. You can use function number 101 also in the above case.

=subtotal(101,C2:C7)

This formula can also find the average of filtered rows. I mean, it excludes filtered-out rows in calculating Averages. But there is one difference.

Unlike the other, i.e., Subtotal(1, this formula will only consider the visible rows in the average calculation.

Difference Between Function Number 1 and 101 in Google Sheets Subtotal Function in Calculating Average

The subtotal(1, formula calculates the average of visible rows in a limited way.

It only excludes the filtered-out rows.

It includes manually hidden and grouped rows. To exclude such rows, use function number 101.

Here is one example with grouped rows and all the above three variations of Google Sheets average formulas.

If I click the “-” button, Google Sheets will collapse the row group, which means rows 2, 3, and 4 will be invisible.

In that case, the formula output will be the same in cells A9 and B9 since the grouping does not affect those formulas, but the cell C9 value changes.

Function number 101 excludes all the hidden rows in the average calculation. Including manually hidden rows, grouped rows, and filtered out rows.

Single Conditional Average that Excluding Filtered or Hidden Rows

Straightaway to the point. To calculate the conditional average in Google Doc Sheets, you can use the function Averageif.

=averageif(B2:B7,"F",C2:C7)

The above Google Sheets Averageif formula conditionally calculates the average.

It returns the average age of females in the list.

Then what about Conditional Averages with visible rows?

There is no Subtotal If function. If there is one, you can use the function number 1 or 101 in it and conditionally average only visible rows.

We can instead use the Subtotal function in a helper or virtual helper column and use that in Averageifs for our purpose.

Yes! Use Averageifs, not Averageif. Find why Averageifs and how to exclude hidden | filtered out | grouped rows in conditional average below.

Multiple Conditional Average that Excluding Filtered or Hidden Rows

I know you have a few questions in your mind.

• How do we exclude filtered | hidden | grouped rows in Averageifs?
• Why am I recommending Averageifs over Averageif?
• What is a helper column or the content in the helper column?

I think I should first answer the third question.

What is a helper column or the content in the helper column?

Column D is the helper column here. It’s an additional column.

This additional column is called a helper column because it can help to simplify formulas or bring new capabilities to them.

In cell D2, you can see one Subototal formula using function number 109 (sum).

=subtotal(109,C2)

I’ve already detailed the difference between function number 1 and 101. The same is applicable to function numbers 9 and 109.

What is the purpose of the formula in cell D2?

It simply returns the value of cell C2 in D2. But if row # 2 is hidden by filtering, row grouping, or hidden manually, the value in cell D2 will become zero!

You must drag this formula down until cell F7 or make it an array formula with the help of the New Map or Byrow function.

If you use the Map or Byrow, empty D2:D7 before inserting the formula as it spills down.

=map(C2:C7,lambda(r, subtotal(109,r)))

Now I can tell you why I am recommending Averageifs over Averageif when you want to omit hidden rows in a conditional Average calculation.

Why Am I Recommending Averageifs over Averageif?

I want to conditionally find the average age in column C based on one single condition.

What is that condition?

I want to find the average age of females (Gender = F) on the list.

There is only one condition, but I want to exclude any hidden rows in the average calculation.

Please see the below image.

The first part is the original data set, and the second part is the same data set, but row number 2 is hidden.

So I’m forced to use two conditions to find the average of visible rows as below.

What are they?

Condition 1:- Values in column B should be “F.”

Condition 2:- Values in column D shouldn’t be 0 (zero).

You can only use multiple conditions in average calculations in Averageifs, not in Averageif.

=AVERAGEIFS(C2:C7,B2:B7,"F",D2:D7,">0")

If you want to use a virtual helper column, replace D2:D7 with the Map formula.

=AVERAGEIFS(C2:C7,B2:B7,"F",map(C2:C7,lambda(r, subtotal(109,r))),">0")

Query Function to Calculate Average in Google Sheets – Filtered and Unfiltered Rows

We can replace the above Averageifs formula with a Query formula as below.

=query(A2:D7,"Select Avg(C) where B='F' and D>0")

In this formula also, I’ve used the helper column D, which contains the Subtotal formula. Please have a look at the “where” clause.

Here is the virtual helper column one.

=query({A2:C7,map(C2:C7,lambda(r, subtotal(109,r)))},"Select Avg(Col3) where Col2='F' and Col4>0")

Conclusion

The above examples of finding the average of visible rows in Google Sheets make us reach one conclusion.

To find the average of hidden | filtered | grouped rows, the function Subtotal is essential in one way or another.

I think, from here onwards, you may not fail in the average calculation with hidden rows.

See you back again, and thanks for the stay.