Array formula with AutoFilter
 

Hello.

About a year ago, someone here helped me with an array formula which would
only sum the visible items in a filtered list. I thought that was done with
SumProduct, but unfortunately, I can't seem to find that example, nor get it
to work right now.

An exampe would be if data were like this:

A B C
1 r 5
1 o 10
3 r 15
1 r 20

We want to sum column C when column B = 'r', but also only when the item is
visible, if autofiltered.

So, pretend a filter were applied so that colum A had to be 1, the visible
items would be:

A B C
1 r 5
1 o 10
1 r 20

and the result of the formula I'm looking for would be 25.... the 5 in the
first row, and the 20 in the last row.

I'm sorry I can't fine where this was done before, but can comeone show me
how to do that? Thank you.

Array formula with AutoFilter
 

Mark,

If the cells are hidden by Autofilter, then a simple =SUBTOTAL(9,C2:C10)
would do the trick (where C2:C10 is the data range in column [C] before
filtering).

Regards,
KL


"mark" wrote in message
...
Hello.

About a year ago, someone here helped me with an array formula which would
only sum the visible items in a filtered list. I thought that was done
with
SumProduct, but unfortunately, I can't seem to find that example, nor get
it
to work right now.

An exampe would be if data were like this:

A B C
1 r 5
1 o 10
3 r 15
1 r 20

We want to sum column C when column B = 'r', but also only when the item
is
visible, if autofiltered.

So, pretend a filter were applied so that colum A had to be 1, the visible
items would be:

A B C
1 r 5
1 o 10
1 r 20

and the result of the formula I'm looking for would be 25.... the 5 in the
first row, and the 20 in the last row.

I'm sorry I can't fine where this was done before, but can comeone show me
how to do that? Thank you.

Array formula with AutoFilter
 

ahh... I got it again. Thanks anyway. I just missed the second condition

Something along the line of:

=SUMPRODUCT(IF(B2:B13="df",1,0)*IF(C2:C13=3,1,0)*( E2:E13))

will do what I need.

sorry... that example doesn't fit my first example.

Got it though. Thanks.

Array formula with AutoFilter
 

Mark,

Your formula below can be optimized as follows:

=SUMPRODUCT((B2:B13="df")*(C2:C13=3)*(E2:E13))

Regards,
KL


"mark" wrote in message
...
ahh... I got it again. Thanks anyway. I just missed the second condition

Something along the line of:

=SUMPRODUCT(IF(B2:B13="df",1,0)*IF(C2:C13=3,1,0)*( E2:E13))

will do what I need.

sorry... that example doesn't fit my first example.

Got it though. Thanks.

Array formula with AutoFilter
 

If the cells are hidden by Autofilter, then a simple =SUBTOTAL(9,C2:C10)
would do the trick (where C2:C10 is the data range in column [C] before
filtering).


but not all of the rows are hidden by the autofilter.. it's a combination of
some being hidden by autofilter (pickable) , and some being excluded through
a countif type settingting for where there's an R in the one column... but
the user doesn't want to hide the items with the O in that colum... just
wants to sum values associated with the visible R rows.

nope.
 

=SUMPRODUCT(IF(B2:B13="df",1,0)*IF(C2:C13=3,1,0)*( E2:E13))

nope, i was wrong. That's not flexible with the autofilter. sorry.

Array formula with AutoFilter
 

Your formula below can be optimized as follows:


You're correct, except I was wrong about the formula. My fault.

Array formula with AutoFilter
 

....then try this one:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$F$13)-ROW($A$2),0))*($B$2:$B$13="df")*($C$2:$C$13=3)*$E$ 2:$E$13)

Regards,
KL


"mark" wrote in message
...
Your formula below can be optimized as follows:


You're correct, except I was wrong about the formula. My fault.

Array formula with AutoFilter
 

yep, that works. thanks.

it is the 'subtotal' that causes it to to not look at the invisible rows?

"KL" wrote:

....then try this one:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$F$13)-ROW($A$2),0))*($B$2:$B$13="df")*($C$2:$C$13=3)*$E$ 2:$E$13)

Regards,
KL


"mark" wrote in message
...
Your formula below can be optimized as follows:


You're correct, except I was wrong about the formula. My fault.

Array formula with AutoFilter
 

it is the 'subtotal' that causes it to to not look at the invisible rows?

it is the combination of SUBTOTAL and OFFSET.

KL

Array formula with AutoFilter
 

it is the combination of SUBTOTAL and OFFSET.


Thanks again for your help. I gave the formula to the person who needed it,
and all is well.

Incidentally, I found the formula I had been looking for from last year, and
as it turns out, it was much the same.