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

Product Code	Flavour	Sales Units
xxx	chocolate	150
yyy	strawberry	230
zzz	banana	98
aaa	mix	110
bbb	mix	120
ccc	mix	85
xxx	chocolate	113
yyy	strawberry	160
aaa	mix	65

<tbody>
</tbody>

Mix Package Info

Product Code	Chocolate	Strawberry	Banana
aaa	50%	20%	30%
bbb	45%	45%	10%
ccc	30%	25%	45%

<tbody>
</tbody>

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

All advice is welcome. Thanks!

 

[matrixpng]

What would be the formular for getting Flavor Sales without Mix Package Info bit?

 

[Aladin Akyurek]

What would be the formular for getting Flavor Sales without Mix Package Info bit?

Do you mean this?

=SUMIF($B$2:$B$10,A15,$C$2:$C$10)

 

[matrixpng]

Another possible solution

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Product Code​	Flavour​	Sales Units​		​
2​	xxx​	chocolate​	150​		Product Code​	Chocolate​	Strawberry​	Banana​
3​	yyy​	strawberry​	230​		aaa​	?	?	?
4​	zzz​	banana​	98​		bbb​	? ​	? ​	?​
5​	aaa​	mix​	110​		ccc​	? ​	? ​	?​
6​	bbb​	mix​	120​
7​	ccc​	mix​	85​
8​	xxx​	chocolate​	113​
9​	yyy​	strawberry​	160​
10​	aaa​	mix​	65​
11​
12​
13​
14​	Flavour​	Sales​
15​	Chocolate​	430​
16​	Strawberry​	500,25​
17​	Banana​	200,75​

<tbody>
</tbody>

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.

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

Additional Question to above with modification on data:

Add I1 = MIX

What would be the formula to derive total sales by Flavor for each Product Code where the queston marks are? I have removed the Flavour Mix bit.

 

[matrixpng]

I have rephrased the question. It should be a SUM-PRODUCT formula but cannot get it right yet.

 

[Aladin Akyurek]

I have rephrased the question. It should be a SUM-PRODUCT formula but cannot get it right yet.

I do not see any rephrasing. Are you looking for a formula or prescribing one? If


=SUMIF($B$2:$B$10,A15,$C$2:$C$10)


is not doing what you intend, try to describe the problem you want to solve. Use the data of post #7 if possible and indicate the desired results which must obtain for that data.

 

[sheetspread]

There are two conditions: product and flavor, so use sumifs (preferable to sumproduct here).

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Product Code	Flavour	Sales Units
2
3	aaa	chocolate	150		Product Code	chocolate	strawberry	banana	mix
4	aaa	strawberry	230		aaa	150	230	0	110
5	ccc	banana	98		bbb	0	160	0	120
6	aaa	mix	110		ccc	113	0	98	150
7	bbb	mix	120
8	ccc	mix	85
9	ccc	chocolate	113		Product Code	chocolate	strawberry	banana	mix
10	bbb	strawberry	160		aaa	150	230	0	110
11	ccc	mix	65		bbb	0	160	0	120
12					ccc	113	0	98	150
Sheet1

Cell Formulas
Range	Formula
F4	=SUMIFS($C$3:$C$11,$A$3:$A$11,$E4,$B$3:$B$11,F$3)
F10	=SUMPRODUCT(($A$3:$A$11=$E10)*($B$3:$B$11=F$9)*$C$3:$C$11)

With the data changed so that all units are direct.

 

[matrixpng]

Beautiful. Exactly what I was asking

 

[Rahul1987]

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>

Hi , usually we use N(if,1) to get index to deliver array , can u pls let me know the use of only “N” ?

 

[Rahul1987]

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>

Hi XOR LX , Can you please explain this formula, I am bit confused now.