SUMPRODUCT with INDEX & MATCH

[D4rwin]

Hi,

I have a list of sales data for a range of products sorted with respective attributes such as flavour, filling size, etc. These products (drinks) are normally packaged by unique flavour which makes counting sales units by flavour very easy by simply specifying the criteria in either sumproduct or sumif. However, in some packages the product flavours are mixed which is then denoted in the sales data attribute as "mix". I have a separate info table representing the flavour split per package (product code) by percent.

I would like to be able to sum the sales units by referencing a flavour (eg. Chocolate) and if an attribute is listed as "mix" I want it to lookup the product code and multiply the sales units with the respective percentage for the mixed package.

I have tried, without success, to combine SUMPRODUCT with the likes of Index & Match but the match function does not seem to work with arrays particularly well. I have played around with it quite a bit by adding separate columns but am not too keen on adding more columns to the sales data.

Here is an example of what it is I would like to achieve:

Sales Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Flavour[/TD]
[TD]Sales Units[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]chocolate[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD]strawberry[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]banana[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]mix[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]bbb[/TD]
[TD]mix[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]ccc[/TD]
[TD]mix[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]chocolate[/TD]
[TD]113[/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD]strawberry[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]mix[/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]

Mix Package Info
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Product Code[/TD]
[TD="align: center"]Chocolate[/TD]
[TD="align: center"]Strawberry[/TD]
[TD="align: center"]Banana[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]30%[/TD]
[/TR]
[TR]
[TD]bbb[/TD]
[TD="align: center"]45%[/TD]
[TD="align: center"]45%[/TD]
[TD="align: center"]10%[/TD]
[/TR]
[TR]
[TD]ccc[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"]45%[/TD]
[/TR]
</tbody>[/TABLE]

I would then like to sum the total units by referencing a flavour.

All advice is welcome. Thanks!

 

[sheetspread]

Since you wish to add 3 rows of mix components for each applicable product, it's probably best to use VBA code. A formula to do this will be long.

 

[XOR LX]

Hi,

Assuming your Sales Data table is in a sheet named Sales Data in the range A1:C10 (with headers in row 1) and that your Mix Package Info table is in a sheet named Mix Package Info in the range A1:D4 (also with headers in row 1), then, if in the Sales Data sheet you enter "mix" in E2:E4 and "chocolate", "strawberry" and "banana" in F2:F4, this array formula** in G2:

=SUM((B$2:B$10=E2:F2)*C$2:C$10*(IFERROR(N(OFFSET('Mix Package Info'!$A$1,MATCH('Sales Data'!$A$2:$A$10,'Mix Package Info'!$A$2:$A$4,0),MATCH('Sales Data'!F2,'Mix Package Info'!$B$1:$D$1,0),,)),1)))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[D4rwin]

Ok, I understand that it is a lot to ask of the standard Excel functions but I was hoping there might be a non-vba solution.

 

[sheetspread]

Well those are the only two ways in Excel to print the exact format you want (the inner join in Access/SQL works too) or you can drop your single column requirement and go with XOR LX's.

I don't think you can write joins in Excel or even powerpivot without an external data source, maybe someone here knows how.

 

[D4rwin]

Hi,

Assuming your Sales Data table is in a sheet named Sales Data in the range A1:C10 (with headers in row 1) and that your Mix Package Info table is in a sheet named Mix Package Info in the range A1:D4 (also with headers in row 1), then, if in the Sales Data sheet you enter "mix" in E2:E4 and "chocolate", "strawberry" and "banana" in F2:F4, this array formula** in G2:

=SUM((B$2:B$10=E2:F2)*C$2:C$10*(IFERROR(N(OFFSET('Mix Package Info'!$A$1,MATCH('Sales Data'!$A$2:$A$10,'Mix Package Info'!$A$2:$A$4,0),MATCH('Sales Data'!F2,'Mix Package Info'!$B$1:$D$1,0),,)),1)))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Wow, that's a pretty hairy, and impressive, formula but it certainly works. The provided sales info is of course a much simplified version of the real database which extends a few thousand rows and with several more criteria. I am therefore hoping to find a solution without any volatile functions (ie OFFSET) which will slow down the workbook calculations considerably. But thank you for your help. I will tinker with it and see what I end up with.

 

[Marcelo Branco]

Another possible solution


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

Product Code​

[/td][td]

Flavour​

[/td][td]

Sales Units​

[/td][td] [/td][td]

Mix Package Info​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

2​

[/td][td]

xxx​

[/td][td]

chocolate​

[/td][td]

150​

[/td][td] [/td][td]

Product Code​

[/td][td]

Chocolate​

[/td][td]

Strawberry​

[/td][td]

Banana​

[/td][/tr]
[tr][td]

3​

[/td][td]

yyy​

[/td][td]

strawberry​

[/td][td]

230​

[/td][td] [/td][td]

aaa​

[/td][td]

50%​

[/td][td]

20%​

[/td][td]

30%​

[/td][/tr]
[tr][td]

4​

[/td][td]

zzz​

[/td][td]

banana​

[/td][td]

98​

[/td][td] [/td][td]

bbb​

[/td][td]

45%​

[/td][td]

45%​

[/td][td]

10%​

[/td][/tr]
[tr][td]

5​

[/td][td]

aaa​

[/td][td]

mix​

[/td][td]

110​

[/td][td] [/td][td]

ccc​

[/td][td]

30%​

[/td][td]

25%​

[/td][td]

45%​

[/td][/tr]
[tr][td]

6​

[/td][td]

bbb​

[/td][td]

mix​

[/td][td]

120​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

7​

[/td][td]

ccc​

[/td][td]

mix​

[/td][td]

85​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

8​

[/td][td]

xxx​

[/td][td]

chocolate​

[/td][td]

113​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

9​

[/td][td]

yyy​

[/td][td]

strawberry​

[/td][td]

160​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

10​

[/td][td]

aaa​

[/td][td]

mix​

[/td][td]

65​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

11​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

12​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

13​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

14​

[/td][td]

Flavour​

[/td][td]

Sales​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

15​

[/td][td]

Chocolate​

[/td][td]

430​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

16​

[/td][td]

Strawberry​

[/td][td]

500,25​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

17​

[/td][td]

Banana​

[/td][td]

200,75​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in B15 copied down
=SUMIF($B$2:$B$10,A15,$C$2:$C$10)+SUMPRODUCT(SUMIF($A$2:$A$10,$E$3:$E$5,$C$2:$C$10)*INDEX($F$3:$H$5,0,MATCH(A15,$F$2:$H$2,0)))

Hope this helps

M.

 

[D4rwin]

Thank you very much Marcelo, yours was a beautiful solution. Exactly what I was looking for!

 

[Marcelo Branco]

Thank you very much Marcelo, yours was a beautiful solution. Exactly what I was looking for!

You are very welcome! Glad for helping

M.

 

[matrixpng]

Another possible solution


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Product Code​

[/TD]
[TD]

Flavour​

[/TD]
[TD]

Sales Units​

[/TD]
[TD][/TD]
[TD]

Mix Package Info​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

xxx​

[/TD]
[TD]

chocolate​

[/TD]
[TD]

150​

[/TD]
[TD][/TD]
[TD]

Product Code​

[/TD]
[TD]

Chocolate​

[/TD]
[TD]

Strawberry​

[/TD]
[TD]

Banana​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

yyy​

[/TD]
[TD]

strawberry​

[/TD]
[TD]

230​

[/TD]
[TD][/TD]
[TD]

aaa​

[/TD]
[TD]

50%​

[/TD]
[TD]

20%​

[/TD]
[TD]

30%​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

zzz​

[/TD]
[TD]

banana​

[/TD]
[TD]

98​

[/TD]
[TD][/TD]
[TD]

bbb​

[/TD]
[TD]

45%​

[/TD]
[TD]

45%​

[/TD]
[TD]

10%​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

aaa​

[/TD]
[TD]

mix​

[/TD]
[TD]

110​

[/TD]
[TD][/TD]
[TD]

ccc​

[/TD]
[TD]

30%​

[/TD]
[TD]

25%​

[/TD]
[TD]

45%​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

bbb​

[/TD]
[TD]

mix​

[/TD]
[TD]

120​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

ccc​

[/TD]
[TD]

mix​

[/TD]
[TD]

85​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

xxx​

[/TD]
[TD]

chocolate​

[/TD]
[TD]

113​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

yyy​

[/TD]
[TD]

strawberry​

[/TD]
[TD]

160​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

aaa​

[/TD]
[TD]

mix​

[/TD]
[TD]

65​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

Flavour​

[/TD]
[TD]

Sales​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

Chocolate​

[/TD]
[TD]

430​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

Strawberry​

[/TD]
[TD]

500,25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

Banana​

[/TD]
[TD]

200,75​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in B15 copied down
=SUMIF($B$2:$B$10,A15,$C$2:$C$10)+SUMPRODUCT(SUMIF($A$2:$A$10,$E$3:$E$5,$C$2:$C$10)*INDEX($F$3:$H$5,0,MATCH(A15,$F$2:$H$2,0)))

Hope this helps

M.

----------------

Great stuff. What would be the correct formula to total Flavor and Sales without the Mix Package Info?