Subtotal function

  • Thread starter Thread starter George Gee
  • Start date Start date
G

George Gee

Hi all.

I wish to use the SUBTOTAL function, to count the number of rows
when using 'Autofilter'

I have this formula: =SUBTOTAL(3,B5:B300)

The problem is that all cells in the range B5:B300 contain formulae,
and is giving the value 296.

Is there any way to edit the formula, to count only values,
in B5:B300?

The formula in B5 is: =IF('Tick sheet'!G5="","",'Tick sheet'!G5)
this is copied down to B300, so the values in B5:B300 are either
a blank cell or "a"

Is this clear?

Many thanks

George Gee
--
 
One way

=SUMPRODUCT(--($B$5:$B$300<>""),--(SUBTOTAL(3,OFFSET($B$5,ROW($B$5:$B$300)-MIN(ROW($B$5:$B$300)),,))))


Regards,

Peo Sjoblom
 
Peo

Full marks to you!
For understanding what I wanted, and supplying the answer.
Many, many thanks!

George Gee

*Peo Sjoblom* has posted this message:
 
My Pleasure, thanks for the feedback

Peo

George Gee said:
Peo

Full marks to you!
For understanding what I wanted, and supplying the answer.
Many, many thanks!

George Gee

*Peo Sjoblom* has posted this message:
Click to expand...
 
Back
Top