Subtotal with Sumproduct and Criteria

[questionking]

Looking to calculate a weighted average if there is a rate in column B, and then to be able to filter on other columns and have the subtotal update with the correct weighted average rate.

Below is a copy of the formula I have thus far, where column B lists the rates and column A lists the values.


=SUMPRODUCT($B$2:$B$1048576,$A$2:$A$1048576)/SUMIF($B$2:$B$1048576,"<>",$A$2:$A$1048576)


How can I get SUBTOTAL to work with this formula?


Thanks in advance for the help, it is much appreciated.

 

[mart37]

I give the cells with rate the name: rng_rate
The cells with value: rng_value

=SUMPRODUCT(SUBTOTAL(9,OFFSET(rng_value,ROW(rng_value)-MIN(ROW(rng_value)),0,1))*SUBTOTAL(9,OFFSET(rng_Rate,ROW(rng_Rate)-MIN(ROW(rng_Rate)),0,1)))/SUMPRODUCT((rng_Rate<>"")*SUBTOTAL(9,OFFSET(rng_value,ROW(rng_value)-MIN(ROW(rng_value)),0,1)))

 

[questionking]

Thank you this worked. I just adjusted the rng_Rate and rng_value to the cells I needed. Your help is much appreciated!