Subtotal

Hi all.

I have formula in A2 =SUBTOTAL(3,A4:A2963)
this counts the number of visible records, (after using Autofilter),
that contain text (ignores blank cells), this works OK.

Problem:

Formula does *not* ignore blank cells, if those cells contain formulae.
Is there a workaround?

Many thanks

George Gee

Try

=SUMPRODUCT(--($A$4:$A$2963""),SUBTOTAL(3,OFFSET($A$4,ROW($A$4 :$A$2963)-MI
N(ROW($A$4:$A$2963)),,)))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"George Gee" wrote in message
...
Hi all.

I have formula in A2 =SUBTOTAL(3,A4:A2963)
this counts the number of visible records, (after using Autofilter),
that contain text (ignores blank cells), this works OK.

Problem:

Formula does *not* ignore blank cells, if those cells contain formulae.
Is there a workaround?

Many thanks

George Gee

Peo

Thanks for the response.
However, A2 returns 0

Regards

George Gee


*Peo Sjoblom* has posted this message:

Try


=SUMPRODUCT(--($A$4:$A$2963""),SUBTOTAL(3,OFFSET($A$4,ROW($A$4 :$A$2963)-MI
N(ROW($A$4:$A$2963)),,)))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"George Gee" wrote in message
...
Hi all.

I have formula in A2 =SUBTOTAL(3,A4:A2963)
this counts the number of visible records, (after using Autofilter),
that contain text (ignores blank cells), this works OK.

Problem:

Formula does *not* ignore blank cells, if those cells contain
formulae. Is there a workaround?

Many thanks

George Gee

Hi George............

How about

=SUBTOTAL(2,A4:A2963)


Vaya con Dios,
Chuck, CABGx3


"George Gee" wrote in message
...
Hi all.

I have formula in A2 =SUBTOTAL(3,A4:A2963)
this counts the number of visible records, (after using Autofilter),
that contain text (ignores blank cells), this works OK.

Problem:

Formula does *not* ignore blank cells, if those cells contain formulae.
Is there a workaround?

Many thanks

George Gee

I don't understand what you mean by that? My formula will count the filtered
list and disregard any blank cells created by formulas

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"George Gee" wrote in message
...
Peo

Thanks for the response.
However, A2 returns 0

Regards

George Gee


*Peo Sjoblom* has posted this message:

Try



=SUMPRODUCT(--($A$4:$A$2963""),SUBTOTAL(3,OFFSET($A$4,ROW($A$4 :$A$2963)-MI
N(ROW($A$4:$A$2963)),,)))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"George Gee" wrote in message
...
Hi all.

I have formula in A2 =SUBTOTAL(3,A4:A2963)
this counts the number of visible records, (after using Autofilter),
that contain text (ignores blank cells), this works OK.

Problem:

Formula does *not* ignore blank cells, if those cells contain
formulae. Is there a workaround?

Many thanks

George Gee

Peo

My mistake!
The formula is perfect.

On this particular worksheet, row and column headers are
set so that they do not show.
And column A is hidden, so I unwittingly pasted the formula into B2.
As there is no data in column A, cell B2 always returned '0'

Appologies if I have caused you a sleepless night! g

Many thanks for your time and trouble.

George Gee



*Peo Sjoblom* has posted this message:

I don't understand what you mean by that? My formula will count the
filtered list and disregard any blank cells created by formulas

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"George Gee" wrote in message
...
Peo

Thanks for the response.
However, A2 returns 0

Regards

George Gee


*Peo Sjoblom* has posted this message:

Try




=SUMPRODUCT(--($A$4:$A$2963""),SUBTOTAL(3,OFFSET($A$4,ROW($A$4 :$A$2963)-MI
N(ROW($A$4:$A$2963)),,)))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"George Gee" wrote in message
...
Hi all.

I have formula in A2 =SUBTOTAL(3,A4:A2963)
this counts the number of visible records, (after using
Autofilter), that contain text (ignores blank cells), this works
OK.

Problem:

Formula does *not* ignore blank cells, if those cells contain
formulae. Is there a workaround?

Many thanks

George Gee

Phew!

Thanks for the feedback, you are one of those rare polite people g

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"George Gee" wrote in message
...
Peo

My mistake!
The formula is perfect.

On this particular worksheet, row and column headers are
set so that they do not show.
And column A is hidden, so I unwittingly pasted the formula into B2.
As there is no data in column A, cell B2 always returned '0'

Appologies if I have caused you a sleepless night! g

Many thanks for your time and trouble.

George Gee



*Peo Sjoblom* has posted this message:

I don't understand what you mean by that? My formula will count the
filtered list and disregard any blank cells created by formulas

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"George Gee" wrote in message
...
Peo

Thanks for the response.
However, A2 returns 0

Regards

George Gee


*Peo Sjoblom* has posted this message:

Try





=SUMPRODUCT(--($A$4:$A$2963""),SUBTOTAL(3,OFFSET($A$4,ROW($A$4 :$A$2963)-MI
N(ROW($A$4:$A$2963)),,)))

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"George Gee" wrote in message
...
Hi all.

I have formula in A2 =SUBTOTAL(3,A4:A2963)
this counts the number of visible records, (after using
Autofilter), that contain text (ignores blank cells), this works
OK.

Problem:

Formula does *not* ignore blank cells, if those cells contain
formulae. Is there a workaround?

Many thanks

George Gee