Sum product index Match

[Mike30]

Hello, I am trying to do a sheet where i look up data and bring back a total. I have 2 spreadsheets 1 with the data and 1 as a front sheet. I need the front sheet to look at the data and bring back the total.

This is the data sheet

		Coverage
Product Code	Product Description	01-Jun	08-Jun	15-Jun	22-Jun
1234	Crisps	<2Weeks	<1Week	<1Week	<1Week
4567	Crisps	<2Weeks	<2Weeks	<3Weeks	<3Weeks
1313	snack	<4Weeks	<4Weeks	<4Weeks	<3Weeks
5555	Drink	>5Weeks	>5Weeks	>5Weeks	>5Weeks
6666	snack	<2Weeks	<1Week	<1Week	<1Week
9876	Crisps	>5Weeks	<5Weeks	>5Weeks	>5Weeks
8546	snack	<4Weeks	<3Weeks	<4Weeks	<3Weeks
7845	Drink	<1Week	<1Week	<1Week	<2Weeks
3265	Drink	<2Weeks	<5Weeks	<4Weeks	<3Weeks

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

This is the front sheet

Weekly Stock Coverage	01/06/2018
Product Description	No. Product Codes	< Min	In Range	> Max
Crisps	3	2	0	1
snack	3	1	2	0
Drinks	3	2	0	1
Other		0	0	0
<Min <1Week and <2Weeks
In Range <3Weeks and <4Weeks
>Max <5Weeks and >5Weeks

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

What I am trying to achieve is... On the Front sheet I have different tabs for each week - 01/06, 08/06, 15/06 etc... I am unable to work out the formula that will look up the date and product description to then count how many criteria there is. so in the above for the 01/06 we have a total of 3 crisp products, and with those 3 codes we have 2 which are <min range (<1Week and <2Weeks) and 1 that is max range (<5Weeks and >5Weeks). Is there a a formula that can do this? I have tried Sumproduct index and match but keep getting #value ????

Sorry if this is a little confusing and long winded.

Mike

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​
1​			Coverage
2​	Product Code	Product Description	1-Jun​	8-Jun​	15-Jun​	22-Jun​
3​	1234​	Crisps	2​	1​	1​	1​
4​	4567​	Crisps	2​	2​	3​	3​
5​	1313​	snack	4​	4​	4​	3​
6​	5555​	Drink	5​	5​	5​	5​
7​	6666​	snack	2​	1​	1​	1​
8​	9876​	Crisps	5​	5​	5​	5​
9​	8546​	snack	4​	3​	4​	3​
10​	7845​	Drink	1​	1​	1​	2​
11​	3265​	Drink	2​	5​	4​	3​
12​
13​
14​
15​	Weekly Stock Coverage				6/1/2018​
16​
17​
18​	Product Description	No. Product Codes	< Min	In Range	> Max
19​	Crisps	3​	2​	0​	1​
20​	snack	3​	1​	2​	0​
21​	Drink	3​	2​	0​	1​
22​	Other	0​	0​	0​	0​
23​
24​	<2weeks<="" td="">
25​	In Range <3Weeks and <4Weeks
26​	>Max <5Weeks and >5Weeks

Sheet: Sheet43

Formula in cell B19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*($C$3:$F$11<>0))

Formula in cell C19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*($C$3:$F$11<=2))

Formula in cell D19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*($C$3:$F$11<=4)*($C$3:$F$11>3))

Formula in cell E19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*($C$3:$F$11>=5))

Copy cell range B19:E19 and paste to cells below.

 

[Mike30]

Hello,

I have done the following formula based on your formulas and I am getting #NA , below is my formula calculation...

=SUMPRODUCT(--('[Stock Coverage Overview v1.xlsx]Stock Report'!E2:E602=A19)*('[Stock Coverage Overview v1.xlsx]Stock Report'!AE2:AQ2=E1)*('[Stock Coverage Overview v1.xlsx]Stock Report'!AE3:AQ602="<1Week")+('[Stock Coverage Overview v1.xlsx]Stock Report'!AE3:AQ602="<2Weeks"))

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​
1​			Coverage
2​	Product Code	Product Description	1-Jun​	8-Jun​	15-Jun​	22-Jun​
3​	1234​	Crisps	<2Weeks	<1Week	<1Week	<1Week
4​	4567​	Crisps	<2Weeks	<2Weeks	<3Weeks	<3Weeks
5​	1313​	snack	<4Weeks	<4Weeks	<4Weeks	<3Weeks
6​	5555​	Drink	>5Weeks	>5Weeks	>5Weeks	>5Weeks
7​	6666​	snack	<2Weeks	<1Week	<1Week	<1Week
8​	9876​	Crisps	>5Weeks	<5Weeks	>5Weeks	>5Weeks
9​	8546​	snack	<4Weeks	<3Weeks	<4Weeks	<3Weeks
10​	7845​	Drink	<1Week	<1Week	<1Week	<2Weeks
11​	3265​	Drink	<2Weeks	<5Weeks	<4Weeks	<3Weeks
12​
13​
14​
15​	Weekly Stock Coverage				6/1/2018​
16​
17​
18​	Product Description	No. Product Codes	< Min	In Range	> Max
19​	Crisps	3​	2​	0​	1​
20​	snack	3​	1​	2​	0​
21​	Drink	3​	2​	0​	1​
22​	Other	0​	0​	0​	0​
23​
24​	<2weeks<="" td="">
25​	In Range <3Weeks and <4Weeks
26​	>Max <5Weeks and >5Weeks

Sheet: Sheet43 (2)

Formula in cell B19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*($C$3:$F$11<>0))


Formula in cell C19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*(C3:F11<="<2Weeks"))


Formula in cell D19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*($C$3:$F$11<="<4Weeks")*($C$3:$F$11>"<3Weeks"))


Formula in cell E19:
=SUMPRODUCT(($B$3:$B$11=A19)*($C$2:$F$2=$E$15)*($C$3:$F$11>=">5Weeks"))