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