Average Subtotals (not including hidden rows) 3

[Robertislearning]

Hi all,

I have the following sample spreadsheet.
A B C
1 Apple 2
3 Apple 2
5 Orange 2

I am looking for a formulas that would give me the average and count of ONLY the VISIBLE rows. For example, I would expect an average of 2 and a count of 3. (Notice that there are hidden rows: Row 2 and Row 4 which have values.

If I do a =count(a1:a5), I get 5. I want to be able to count only visible items in column A.

This is an attempt to average subtotals.

Thanks in advance!

 

[kjv1611]

If it's not a terribly large number of rows, then you can just hold the ctrl key while selecting all the cells you want to include in your Sum/Average/Coung formula (select all cells or range in parenthesis, then hit ctrl, then select individually the cells (not Shift)

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[TonyJollans]

Hi Robertislearning,

IF the hidden rows are hidden due to filtering, then you can use the SUBTOTAL function.

If all cells are visible, then ..

[blue][tt]SUBTOTAL(2,A1:A5)[/tt][/blue] will give the same as [blue][tt]COUNT(A1:A5)[/tt][/blue] (5 in this example)

If rows 2 and 4 are FILTERED out, then

[blue][tt]COUNT(A1:A5)[/tt][/blue] will still give 5, but ..
[blue][tt]SUBTOTAL(2,A1:A5)[/tt][/blue] will give 3

This effect, however, only occurs when the range is filtered but note that it does take account of (i.e. ignore) manually hidden rows when a filter is in effect - the method of hiding is not important (indeed Excel doesn't know the difference), what is important is that there is a filter in effect.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[KenWright]

Unless............. you have XL2003 in which case there is now an argument to allow you to filter manually hidden rows as well

First Col is normal argument number with last col being function performed.

Second col is new arguments introduced in 2003 to allow for manually hidden rows to be excluded. All arguments old and new are available in 2003.

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Robertislearning]

Gentlemen,

Thank you very much for this valuable information. I sincerely appreciate it!

Robert.