Sumproduct for finding averages

I have attached a spreadsheat. I am using the following formula =SUMPRODUCT(($D$6:$BX$6=DH$6)*($D10:$BX10)*($D$9:$BX$9))/SUMIFS($D$9:$BX$9,$D$6:$BX$6,DH$6) in cell DH10 to calculate the YTD average for row 10 ($ amount) and 12 (unit number). For some reason the average dollar amount is coming in way too small. Any help? Thanks.

An average is worked out as a sum divided by a count of the number of items. Why don't you use SUMIFS()/COUNTIFS ? Or alternatively, use AVERAGEIFS.

Hope this helps.

Pete

Hi,

There is something wrong with your formula, but I'm not sure what it is because I don't understand exactly what you are attempting to calculate. So I have 2 suggestions which may (hopefully) get you on your way:

1. Change the formula in DH10 to

Formula:

=SUMPRODUCT(($D$6:$BX$6=DH$6)*($D10:$BX10)*($D$9:$BX$9))/SUMIFS($D$9:$BX$9,$D$6:$BX$6,DH$6,$D$10:$BX$10,">0")

This gives an average of $188.29.
2. Change the formula in DH10 to

Formula:

=SUMPRODUCT(($D$6:$BX$6=DH$6)*($D11:$BX11)*($D$9:$BX$9))/SUMIFS($D$9:$BX$9,$D$6:$BX$6,DH$6)

This gives an average of $210.03.

I hope one of these is the solution that you are looking for

Pete, the OP is using SUMIFS because the divisor is the sum of the cells in row 9, as these cells represent the number of units to average over