Subtotal Sumproduct?

[aquapowers]

Is it possible to use SUBTOTAL in conjunction with SUMPRODUCT? I'm using the following formula to calculate a weighted average price increase.
=SUMPRODUCT(N5:N3400,AQ5:AQ3400)/SUM(AQ5:AQ3400)
column N= price increase percentage
column AQ= sales

I would like to add SUBTOTAL to the formula in order to see the various weighted average increase by filtered selections.

Thank you in advance!

 

[JoeMo]

SUMPRODUCT is not one of the functions offered within SUBTOTAL. Untested but you might try this, which should handle changes with filtering.

=SUBTOTAL(106,N5:N3400,AQ5:AQ3400)/SUBTOTAL(109,AQ5:AQ3400)

EDIT: after looking at this a bit, its not going to give you what you want.

 

[Eric W]

Try:

Book1 (version 1).xlsb
	N																													AQ	AR
3																															18.67%
4																															16.36%
5	10%																													100
6	20%																													200
7	30%																													300
9	8%																													500
Sheet19

Cell Formulas
Range	Formula
AR3	AR3	=SUMPRODUCT(N5:N3400,AQ5:AQ3400)/SUM(AQ5:AQ3400)
AR4	AR4	=SUMPRODUCT(N5:N3400,AQ5:AQ3400,SUBTOTAL(102,OFFSET(AQ5,ROW(AQ5:AQ3400)-ROW(AQ5),0)))/SUMPRODUCT(AQ5:AQ3400,SUBTOTAL(102,OFFSET(AQ5,ROW(AQ5:AQ3400)-ROW(AQ5),0)))

Your formula in AR3, the adjusted version in AR4. I have one row hidden, and you can see that the value is different.