Count all except filtered data
 

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$1920)),--($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

Count all except filtered data
 

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


Vaya con Dios,
Chuck, CABGx3



"andy62" wrote:

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$1920)),--($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

Count all except filtered data
 

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

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?

--
Biff
Microsoft Excel MVP


"andy62" wrote in message
...
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$1920)),--($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