Subtotal

[George Gee]

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

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]

Peo

Thanks for the response.
However, A2 returns 0

Regards

George Gee


*Peo Sjoblom* has posted this message:

 

[CLR]

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

How about

=SUBTOTAL(2,A4:A2963)


Vaya con Dios,
Chuck, CABGx3

 

[Peo Sjoblom]

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]

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:

 

[Peo Sjoblom]

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)