Count all except filtered data

A

andy62

I have used versions of the basic formula below to count the records
remaining after application of the advanced filter:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$19)-ROW($A$1),,1)),--($C$2:$C$19>20)),--($C$2:$C$19<=50))

Is there any way to change the OFFSET part to reference the opposite: all
rows HIDDEN after application of the advanced filter? I don't see this
question having been asked before, which is surprising.

TIA
 
C

CLR

Maybe just COUNTA the whole range you're interested in and then just subtract
your filtered result......


Vaya con Dios,
Chuck, CABGx3
 
T

T. Valko

I don't see this question having been asked before
which is surprising.
Click to expand...

I've never seen it either!

The formula counts all visible rows that meet the condition. So, if you want
the "opposite", wouldn't it just be the total count of all rows minus the
result of your formula?
 

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

Similar Threads

I'm trying to do a "count" function at the end of a dynamic pivot table 0
Getting Text data using the Data - Subtotal feature 1
Help with formula 3
Counting dates in filtered column 1
Filtered Counting 5
Formula help with greater than and count if 1
count specific subtotal values only 9
unique records formula 9

Top