Sumproduct for all numbers in one column and all criteria in second column

[ebgordon1]

I have a problem where I am trying to determine percentage averages for multiple customers in a given period. For example in the graphic below, "Cust 1" has 10 units at a 10 percent discount, Customer 2 had 20 units at a 5% discount and so on. For the quarter, the average discount is calculated at 10% in cell C12 using the formula ((C6*C7)+(C8*C9)+(C10*C11)+(C12*C13))/C15.




-- removed inline image ---



This is an example, but I have cases with dozens and dozens of customers. It seems like a job for sumproduct but when I use that, the result is obviously 0 as the 10 Units for "Cust 1" multiplies by the 0 generated where cell B3 returns a zero since it isn't a unit.

Is there a formulaic way to do what I am calculating above?

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum.
Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[ebgordon1]

[TABLE="width: 282"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]Cust 1[/TD]
[TD]Units[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Discount[/TD]
[TD="align: right"]10%[/TD]
[/TR]
[TR]
[TD]Cust 2[/TD]
[TD]Units[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Discount[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD]Cust 3[/TD]
[TD]Units[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Discount[/TD]
[TD="align: right"]7%[/TD]
[/TR]
[TR]
[TD]Cust 4[/TD]
[TD]Units[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Discount[/TD]
[TD="align: right"]23%[/TD]
[/TR]
</tbody>[/TABLE]


Above is the detail.

I was able to resolve the problem by offsetting the second array in the sumproduct as below. By offsetting the arrays, the match for the first "Units" multiplies by the first match of "Discount". Thanks for following up.

SUMPRODUCT(($B$2:$B$9="Units")*$C$2:$C$9,($B$3:$B$10="Discount")*$C$3:$C$10)/C17