Sumproduct with filters

[Andy]

I am trying to get a weighted average based on item cost, potential cost
increase, and peices sold. I entered a formula using sumproduct which works,
except I can't seem to figure out how to make it work for a filtered set of
data.

Column AJ = design improvement cost increase
Column AP = sold qty
Column U = item cost

=SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845)

I am hoping to be able to filter based on a product category and see the
weighted average cost increase for a potential design improvement by category.

any help would be greatly appreciated.

 

[Mike H]

Andy,

Sumproduct gets a bit more complex on a filtered range, try this


=SUMPRODUCT(SUBTOTAL(9,OFFSET(AJ4,ROW(AJ5:AJ845)-ROW(AJ4),,1)),SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)))/SUMPRODUCT(SUBTOTAL(9,OFFSET(AP4,ROW(AP5:AP845)-ROW(AP4),,1)),SUBTOTAL(9,OFFSET(U4,ROW(U5:U845)-ROW(U4),,1)))

Mike

 

[Mike H]

Hi,

With the analysis toolpak loaded


=MROUND(A1,50)

or without

=ROUND(A1/50,0)*50

Mike

 

[Mike H]

Hmm,

Replied to wrong thread

Hi,

With the analysis toolpak loaded


=MROUND(A1,50)

or without

=ROUND(A1/50,0)*50

Mike

 

[Stefi]

SUMPRODUCT can also filter, try this:
If data by which you want to filter are, say in column Z and the value to be
filtered out is in cell AA1 then

=(SUMPRODUCT(AJ5:AJ845,AP5:AP845)/SUMPRODUCT(AP5:AP845,U5:U845))*SUMPRODUCT(--(Z5:Z845=$AA$1))

Regards,
Stefi

„Andy†ezt írta:

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(AJ5:AJ845,ROW(AJ5:AJ845)-ROW(AJ5),,1)),AP5:
AP845)/SUMPRODUCT(SUBTOTAL(9,OFFSET(AP5:AP845,ROW(AP5:AP845)-ROW(AP5),,1)
),U5:U845)

Hope this helps!

 

[Andy]

Thanks for the quick reply Mike. Looks like i may get the report out today
after all.

Andy

 

[Mike H]

Glad I could help

Thanks for the quick reply Mike. Looks like i may get the report out today
after all.

Andy