Subtotal function
 

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

"George Gee" wrote:

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
--

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:

One way


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


Regards,

Peo Sjoblom

"George Gee" wrote:

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
--

My Pleasure, thanks for the feedback

Peo

"George Gee" wrote:

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:

One way


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


Regards,

Peo Sjoblom

"George Gee" wrote:

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
--