Complex Nested Array Formula - Sumproduct with Offset rows

[Gimics]

Hello,

I'm looking for some help in creating a complex nested array formula for evaluating a few columns of data. I would like to find the sumproduct of two differing rows, based on nested lookups. Here's an example table of data similar to mine:

This is an oversimplified example of what I am trying to do. Products are sold at different weeks throughout the year, but purchased in bulk. Purchases can have discounts applied to the bulk order. I would like to use a formula to calculate the total discount applied to the purchases of each product type. Here is an example of my data and a summary of what I am trying to achieve:

Excel 2012

	A	B	C	D	E	F	G
PRODUCT
APPLE
APPLE
APPLE
ORANGE
APPLE
APPLE
ORANGE
APPLE
TOTAL
SUMMARY:
UNITS
DISCOUNT
AVERAGE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]WEEK[/TD]
[TD="align: right"]WEEK P[/TD]
[TD="align: right"]UNITS[/TD]
[TD="align: right"]UNITS P[/TD]
[TD="align: right"]DISCOUNT[/TD]
[TD="align: right"]TOTAL[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 8,360 [/TD]
[TD="align: right"] 33,398 [/TD]
[TD="align: right"] $ 5.00 [/TD]
[TD="align: right"] $ 166,990 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 8,306 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 3,265 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 9,146 [/TD]
[TD="align: right"] 19,109 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"] 4,321 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"] 7,150 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"] 3,418 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"] 8,541 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] 52,507 [/TD]
[TD="align: right"] 52,507 [/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 166,990 [/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]APPLE[/TD]
[TD="align: right"]ORANGE[/TD]
[TD="align: right"] TOTAL [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"] 39,943 [/TD]
[TD="align: right"] 12,564 [/TD]
[TD="align: right"] 52,507 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"] $ 121,260 [/TD]
[TD="align: right"] $ 45,730 [/TD]
[TD="align: right"] $ 166,990 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"] $ 3.04 [/TD]
[TD="align: right"] $ 3.64 [/TD]
[TD="align: right"] $ 3.18 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

I am trying to create a formula that would replicate the values of cells B14 and C14. In this example, a discount is applied to the purchases (UNITS P) in week 1. Column B indicates the actual week and Column C indicates the week purchased. Column D indicates the weekly volume and column E indicates the volume purchased. Column F is the discount applied to the units purchased in that week.

Currently, cells B14 and C14 are manually summing the product of the UNITS x the DISCOUNT for each PRODUCT that was purchased at a discount.

A formula would have to evaluate column A to find a match for the product in row 12; this match would provide the units in column D for that week. The formula would then have to evaluate which week those units were purchased in (match the row value of column C to column B) and return the discount value for that week (Column F).

In this example, for "ORANGE", the formula would return week 4 and week 7 units. It would then identify week each purchase was made (week 1 and week 4 respectively), and then multiply the units by the discount for those weeks ($5.00 and null, respectively).

I actually have 52 weeks of data, with multiple "PRODUCTS" and "DISCOUNTS" happening all over, which significantly complicates this workbook if it were to be processed manually.

That's a lot of typing.. hopefully I've covered this off with enough clarity that someone can make sense of things.

I appreciate any help in advance.

 

[Aladin Akyurek]

How does $ 121,260 obtain if done manually?

 

[Gimics]

How does $ 121,260 obtain if done manually?

Hey Aladin - I provided an example of this for "ORANGE" as there were fewer instances to write out. The logic is the same for APPLE. All of the APPLE week units (COLUMN D), multiplied by their respective discounts (COLUMN F), based on the week they were purchased (indicated in COLUMN C). Week 1, 2, 3 and 5 units x week 1 discount, and week 6 and 8 units x week 4 discount.

 

[Aladin Akyurek]

Hey Aladin - I provided an example of this for "ORANGE" as there were fewer instances to write out. The logic is the same for APPLE. All of the APPLE week units (COLUMN D), multiplied by their respective discounts (COLUMN F), based on the week they were purchased (indicated in COLUMN C). Week 1, 2, 3 and 5 units x week 1 discount, and week 6 and 8 units x week 4 discount.

Care to do this with the figures which are available in your exhibit?

 

[Gimics]

Care to do this with the figures which are available in your exhibit?

You bet; I'll explain the calculation procedurally:
1) Which weeks include APPLE: (COLUMN B) 1,2,3,5,6,8
2) What are the quantities sold for those weeks: (COLUMN D) 8,360; 8,306; 3,265; 4,321; 7,150; 8,541
3) What are the discounts for those weeks
a) What weeks were the purchases made: (COLUMN C) 1,1,1,1,4,4
b) What discounts relate to those weeks: (COLUMN F) $5,$5,$5,$5,null,null

4) What is the discount for each of those weeks: 8,360 x $5; 8,306 x $5; 3,265 x $5; 4,321 x $5; 7,150 x null; 8,541 x null - this is the array from 2) x the array from 3b)
5) What is the sum of the products: $121,260 - the sum of the results from 4)

 

[Aladin Akyurek]

Why 4,321 x $5, but 7,150 x null?

What is the DISCOUNT value for ORANGE?

 

[Gimics]

Why 4,321 x $5, but 7,150 x null?

What is the DISCOUNT value for ORANGE?

Aladin - the discount applied is determined in Column C. 4,321 were sold in week 5 - week 5 was purchased in week 1 (see Column C). Week 1 purchases have a discount of $5 (see column F). 7,150 relates to week 6 - week 6 was purchased in week 4 (see Column C). Week 4 purchases do not have a discount (no value in column F).

The procedural walk through I provided would have been for the formula in cell B14. The calculation for apples (Cell B14) would reference to cell B12 to evaluated apples. C14 would reference C12 to evaluate oranges. Oranges would have different discount rates dependent on the week they were purchased in (see Column C for the purchase week).

 

[Aladin Akyurek]

In B13 enter and copy to C13:

=SUMIFS($D$2:$D$9,$A$2:$A$9,B$12)

In D13 enter:

=SUM(B13:C13)

In B14 enter and copy to C14:

=SUMPRODUCT($D$2:$D$9,--($A$2:$A$9=B$12),SUMIFS($F$2:$F$9,$B$2:$B$9,$C$2:$C$9))

In D14 enter:

=SUM(B14:C14)

In B15 enter and copy to D15:

=B14/B13

 

[Gimics]

Incredible - not even an array formula? Was just looking for the B14/C14 part... and it looks like it works great on the test data. Will try again on the real thing tomorrow.

Your help is much appreciated - thanks for being patient with the questions/answers.

 

[Aladin Akyurek]

Incredible - not even an array formula? Was just looking for the B14/C14 part... and it looks like it works great on the test data. Will try again on the real thing tomorrow.

Your help is much appreciated - thanks for being patient with the questions/answers.

You are welcome.