Countif Hidden rows with criteria

[Puffin617]

Hi,

Currently My COUNTIF statement counts all the cell values which contain values above and below 2 calculated values (+- 2*SQRT(2/n)

=COUNTIF($R$2:$R$79,"<"&-2*SQRT(2/COUNT($R$2:$R$79)))+COUNTIF($R$2:$R$79,">"&2*SQRT(2/COUNT($R$2:$R$79)))

How do I add a second counter that ignores the ones that are in hidden rows?

Thanx

 

[T. Valko]

Hi,

Currently My COUNTIF statement counts all the cell values which contain values above and below 2 calculated values (+- 2*SQRT(2/n)

=COUNTIF($R$2:$R$79,"<"&-2*SQRT(2/COUNT($R$2:$R$79)))+COUNTIF($R$2:$R$79,">"&2*SQRT(2/COUNT($R$2:$R$79)))

How do I add a second counter that ignores the ones that are in hidden rows?

Thanx

What version of Excel are you using?

 

[Puffin617]

2010

 

[T. Valko]

2010

I would use cells to hold the value range otherwise the formula will be quite long.

T1:

=-2*SQRT(2/SUBTOTAL(102,R2:R79))

U1:

=2*SQRT(2/SUBTOTAL(102,R2:R79))

Then:

<?XML:NAMESPACE PREFIX = T1))+SUMPRODUCT(SUBTOTAL(102,OFFSET(R2,ROW(R2 /><T1))+SUMPRODUCT(SUBTOTAL(102,OFFSET(R2,ROW(R2:R79)-ROW(R2),,1)),--(R2:R79>=SUMPRODUCT(SUBTOTAL(102,OFFSET(R2,ROW(R2:R79)-ROW(R2),,1)),--(R2:R79 < T1))+SUMPRODUCT(SUBTOTAL(102,OFFSET(R2,ROW(R2:R79)-ROW(R2),,1)),--(R2:R79 > U1))
</T1))+SUMPRODUCT(SUBTOTAL(102,OFFSET(R2,ROW(R2:R79)-ROW(R2),,1)),--(R2:R79>

 

[pgc01]

Hi

Another option:

=SUMPRODUCT(--(ABS(SUBTOTAL(109,OFFSET(R2,ROW(R2:R79)-ROW(R2),0)))>2*SQRT(2/SUBTOTAL(102,R2:R79))))

 

[Puffin617]

Thanx so much ) Works like a charm

Just wished I could understand this "subtotal" and "offset" thing better

 

[Sektor]

Use new function AGGREGATE.