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

"Andy" wrote:

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.