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

ebgordon1

New Member
Joined
Aug 14, 2017
Messages
2
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?
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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

New Member
Joined
Aug 14, 2017
Messages
2
Q1
Cust 1Units10
Discount10%
Cust 2Units20
Discount5%
Cust 3Units15
Discount7%
Cust 4Units11
Discount23%

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>


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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,015
Messages
5,599,351
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top