Sum according to Auto filter non visible cells

**ElmerS** · 08-09-2009, 04:44 PM

Hi,

The whole "story" can be found inside the attached WB.

I tried using SUBTOTATL but no luck.

As always, I prefer without helper column(s) and/or VBA - but If not - I will, probably, have to compromise.

Thanks, Elm

**contaminated** · 08-09-2009, 05:04 PM

U can use array formulae to solve your issue.
In D27

Please Login or Register  to view this content.

D25 is 5
But i think this is not what you're lookin' for.

VBA codin' is required here in order to do exactly what you want.
In my opinion here are no formulas that able to sum only visible cells... if only not UDF..

**ncmay** · 08-09-2009, 05:19 PM

You can use subtotal if you filter column A greater than 100 then filter column B greater than 5.

Norman

**ElmerS** · 08-09-2009, 05:22 PM

Thank you, ContaminatedWitExce.

Of course you are absolutely right because it was my mistake.

Cell B11 should also be 6.00 - and in that case your formula is not appropriate.

Still waiting for a result one way or the other.

btw. the empty cells in col. "A" are not "zeros" - they may be treated as "BLANKS".
(This is the way i got that WB).
=========================

Thank you too, ncmay,

This is a workaround and can be considered but I still wonder if there is some formula that can do it without filtering col. "B".

Elm

**martindwilson** · 08-09-2009, 07:11 PM

try
array entered
=SUMPRODUCT((SUBTOTAL(3,OFFSET(B5:B24,ROW(B5:B24)-MIN(ROW(B5:B24)),,-1)))*((B5:B24)>5)*(A5:A24))
note your sheet has text in cells A5:A24 which stops this working
select col a /data /text to columns /click finish
that should sort it ive done it on attached formula is in d29
notes
range (B5:B24) is being checked for >5
range A5:A24 is the one being summed hence the offset -1 col b to col a
OFFSET(B5:B24,ROW(B5:B24)-MIN(ROW(B5:B24)),,-1))

**ElmerS** · 08-09-2009, 11:06 PM

Perfect Score, Martin.

One small remark: by using SUMPRODUCT, in that case, you don't need it to be an Array formula.

Thanks a lot, Elm

**Domenic** · 08-10-2009, 03:03 AM

Here's another way...

=SUMPRODUCT(--(B5:B25>5),SUBTOTAL(9,OFFSET(A5:A25,ROW(A5:A25)-ROW(A5),0,1)

**martindwilson** · 08-10-2009, 07:11 AM

i dont know why i put array entered anyway!

**ElmerS** · 08-10-2009, 12:36 PM

Originally Posted by Domenic

Here's another way...

=SUMPRODUCT(--(B5:B25>5),SUBTOTAL(9,OFFSET(A5:A25,ROW(A5:A25)-ROW(A5),0,1)

Thanks, Domenic.

**DonkeyOte** · 08-10-2009, 12:45 PM

and as you no doubt know generally speaking Volatile Sumproducts will kill performance -- volatile here given use of OFFSET ... and obviously filtering is a Volatile action in it's own right (in part because of SUBTOTAL Function)