Have a formula consider only visible rows

[tk19]

I have a formula, something like

=COUNTIF(A1:A100,1)

Which will count all cells equal to 1. Is there a way I can have it only count the visible (i.e., its row is not hidden) cells that are equal to 1?


I actually had another question, whether I can do conditional formatting that will either hide a row or change its row height based on the value of a particular cell in that row. I'm guessing that the only way I can do that is through a macro in the Workbook_Change() event.

 

[T. Valko]

Further explanation is in order. I have two worksheets. Worksheet1 contains the data. The table range is A2:CH5. Row 2 contains the headers and rows 3 thur 5 contains the data. Table name is Table1. Worksheet2 contains a summary of the data. In cell H2 on sheet2 I want to show the average of the visible rows for field3 if the value of field5 for each visible row is equal to 18. The formula that I am using in cell H2 of sheet2 is =Averageif(vis(Table1[Field5]),18,vis(Table1[Field3])). If I remove the vis functyion from the equation I always the the average of all three rows. If I filter out (hide) a row I want the average to only reflect the other two rows (the visible rows). The vis function is the one that I got from this thread.

Hope this clears things up and thanks in advance for your interst and willingness to help.

I never use that structured referencing.

See if you can translate this formula to the structured reference style.

Let's assume this is your data:

Book1
	A	B	C
5	Region	Age	Amount
6	West	25	89
7	North	23	10
8	South	18	3
9	North	18	5
10	South	23	98
11	South	21	33
12	East	18	43
13	North	18	38
14	East	19	29
15	North	22	2
16	West	23	99
17	East	18	14
18	South	18	29
19	South	23	70
20	South	18	61
21	West	18	59
22	North	18	84
Sheet1

You want to get the average of Amount when Age = 18.

A2 = 18

Array entered**:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(C6,ROW(C6:C22)-ROW(C6),0,1)),IF(B6:B22=A2,C6:C22)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If you were to filter on Region by North:

Book1
	A	B	C
5	Region	Age	Amount
7	North	23	10
9	North	18	5
13	North	18	38
15	North	22	2
22	North	18	84
Sheet1

The formula result will be 42.3.

 

[jma49]

Your approach works great. Thanks again for you assistance.

 

[T. Valko]

Your approach works great. Thanks again for you assistance.

You're welcome. Thanks for the feedback!

 

[I Am Herenow]

Hi, could anyone possibly create the code for me for a visible-only COUNTA function, like you did for COUNTIF?

Thanks very much

 

[T. Valko]

Hi, could anyone possibly create the code for me for a visible-only COUNTA function, like you did for COUNTIF?

Thanks very much

For a simple COUNTA on a filtered list:

=SUBTOTAL(3,A5:A20)

 

[I Am Herenow]

Thanks, but the COUNTA was actually part of a CONCATENATE (which used to work fine w/o filters, but would not react to rows being hidden by a filter), and now that it has been replaced by a CONCATENATE including a SUBTOTAL, the cell just displays the formula written out, instead of the result of the formula.

 

[T. Valko]

Thanks, but the COUNTA was actually part of a CONCATENATE (which used to work fine w/o filters, but would not react to rows being hidden by a filter), and now that it has been replaced by a CONCATENATE including a SUBTOTAL, the cell just displays the formula written out, instead of the result of the formula.

If the cell displays the formula maybe the cell is formatted as text?

Change the cell format to General then re-enter the formula.

 

[I Am Herenow]

Thanks!

 

[T. Valko]

Thanks!

You're welcome!

 

[tigger83]

The easiest way to count ONLY cells that are NOT hidden by a filter is this: =subtotal(103,A1:A100,1)

If you use =subtotal(3,A1:A100,1) it will count both visible AND hidden rows.

Look in HELP - Subtotal function sytax - function number. Cool things hidden there...SUM, MAX, MIN, PRODUCT, COUNT, COUNTA... And you can adapt to having only filtered data in a range considered...or all data in a range considered!