Sumproduct with filters
 

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.

Sumproduct with filters
 

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.

Sumproduct with filters
 

Hi,

With the analysis toolpak loaded


=MROUND(A1,50)

or without

=ROUND(A1/50,0)*50

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.

Sumproduct with filters
 

Hmm,

Replied to wrong thread

"Mike H" wrote:

Hi,

With the analysis toolpak loaded


=MROUND(A1,50)

or without

=ROUND(A1/50,0)*50

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.

Sumproduct with filters
 

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:

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.

Sumproduct with filters
 

Try...

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

Hope this helps!

In article ,
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.

Sumproduct with filters
 

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

Andy

"Mike H" wrote:

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.

Sumproduct with filters
 

Glad I could help

"Andy" wrote:

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

Andy

"Mike H" wrote:

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.