Subtotal

G

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
 
P

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

George Gee

Peo

Thanks for the response.
However, A2 returns 0

Regards

George Gee


*Peo Sjoblom* has posted this message:
 
P

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

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top