sumproduct with unhidden data

[vincentwady]

Hi, all

I have a simple question about how to do sumproduct with the unhidden
data within the rage. The function I want is pretty like
subtotal(109,..) fuction, will only do the calculation based on the
unhidden data after I apply the auto filter. For example,

A B C D
100 2
200 4
300 3
400 1
500 5 500 5
600 2 600 2
700 5 700 5
800 4 800 4
900 5 900 5
1000 9 1000 9
26200 23900 <= sumproduct

Column A and B is original range, so 26200=sumproduct(A1:A10,B1:B10)
Column C and D is just represent when I use a auto fileter for column
A >= 500, and I want to capture the new data's weighted average in the
same cell, but don't know which function or how I should adjust the
sumproduct to do that. But the goal is 23900=sumproduct(A1:A10,B1:B10)
but only calculate the result data based on how I filter it.

Does anyone how to do this? Maybe I need to add some reference flag to
do that?

Thank you

Vincent

 

[JMB]

assuming your data is in A1:B11 (with a header in row 1), try:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1,ROW(INDIRECT("1:"&ROWS(A2:A11))),0)),B2:B11)

with XL03 or later, I believe you can use 109 option for subtotal to include
rows hidden by the user.