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[TABLE="width: 477"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 4"]Coverage[/TD]
[/TR]
[TR]
[TD]Product Code[/TD]
[TD]Product Description[/TD]
[TD]01-Jun[/TD]
[TD]08-Jun[/TD]
[TD]15-Jun[/TD]
[TD]22-Jun[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Crisps [/TD]
[TD]<2Weeks[/TD]
[TD]<1Week[/TD]
[TD]<1Week[/TD]
[TD]<1Week[/TD]
[/TR]
[TR]
[TD]4567[/TD]
[TD]Crisps [/TD]
[TD]<2Weeks[/TD]
[TD]<2Weeks[/TD]
[TD]<3Weeks[/TD]
[TD]<3Weeks[/TD]
[/TR]
[TR]
[TD]1313[/TD]
[TD]snack[/TD]
[TD]<4Weeks[/TD]
[TD]<4Weeks[/TD]
[TD]<4Weeks[/TD]
[TD]<3Weeks[/TD]
[/TR]
[TR]
[TD]5555[/TD]
[TD]Drink[/TD]
[TD]>5Weeks[/TD]
[TD]>5Weeks[/TD]
[TD]>5Weeks[/TD]
[TD]>5Weeks[/TD]
[/TR]
[TR]
[TD]6666[/TD]
[TD]snack[/TD]
[TD]<2Weeks[/TD]
[TD]<1Week[/TD]
[TD]<1Week[/TD]
[TD]<1Week[/TD]
[/TR]
[TR]
[TD]9876[/TD]
[TD]Crisps[/TD]
[TD]>5Weeks[/TD]
[TD]<5Weeks[/TD]
[TD]>5Weeks[/TD]
[TD]>5Weeks[/TD]
[/TR]
[TR]
[TD]8546[/TD]
[TD]snack[/TD]
[TD]<4Weeks[/TD]
[TD]<3Weeks[/TD]
[TD]<4Weeks[/TD]
[TD]<3Weeks[/TD]
[/TR]
[TR]
[TD]7845[/TD]
[TD]Drink[/TD]
[TD]<1Week[/TD]
[TD]<1Week[/TD]
[TD]<1Week[/TD]
[TD]<2Weeks[/TD]
[/TR]
[TR]
[TD]3265[/TD]
[TD]Drink[/TD]
[TD]<2Weeks[/TD]
[TD]<5Weeks[/TD]
[TD]<4Weeks[/TD]
[TD]<3Weeks[/TD]
[/TR]
</tbody>[/TABLE]


This is the front sheet

[TABLE="width: 331"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD="colspan: 4"]Weekly Stock Coverage [/TD]
[TD]01/06/2018[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Product Description[/TD]
[TD]No. Product Codes[/TD]
[TD]< Min[/TD]
[TD]In Range[/TD]
[TD]> Max[/TD]
[/TR]
[TR]
[TD]Crisps[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]snack[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Drinks[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]<Min <1Week and <2Weeks[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]In Range <3Weeks and <4Weeks[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]>Max <5Weeks and >5Weeks[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Product Code[/td][td]Product Description[/td][td]

1-Jun​

[/td][td]

8-Jun​

[/td][td]

15-Jun​

[/td][td]

22-Jun​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1234​

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

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

4567​

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

2​

[/td][td]

2​

[/td][td]

3​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

1313​

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

4​

[/td][td]

4​

[/td][td]

4​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

5555​

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

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

6666​

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

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

9876​

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

5​

[/td][td]

5​

[/td][td]

5​

[/td][td]

5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

8546​

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

4​

[/td][td]

3​

[/td][td]

4​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

7845​

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

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

3265​

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

2​

[/td][td]

5​

[/td][td]

4​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]Weekly Stock Coverage[/td][td][/td][td][/td][td][/td][td]

6/1/2018​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]Product Description[/td][td]No. Product Codes[/td][td]< Min[/td][td]In Range[/td][td]> Max[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

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

3​

[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

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

3​

[/td][td]

1​

[/td][td]

2​

[/td][td]

0​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

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

3​

[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

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

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td]<2weeks<="" td="">[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td]In Range <3Weeks and <4Weeks[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td]>Max <5Weeks and >5Weeks[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet43[/td][/tr][/table]

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]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Product Code[/td][td]Product Description[/td][td]

1-Jun​

[/td][td]

8-Jun​

[/td][td]

15-Jun​

[/td][td]

22-Jun​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1234​

[/td][td]Crisps[/td][td]<2Weeks[/td][td]<1Week[/td][td]<1Week[/td][td]<1Week[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

4567​

[/td][td]Crisps[/td][td]<2Weeks[/td][td]<2Weeks[/td][td]<3Weeks[/td][td]<3Weeks[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

1313​

[/td][td]snack[/td][td]<4Weeks[/td][td]<4Weeks[/td][td]<4Weeks[/td][td]<3Weeks[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

5555​

[/td][td]Drink[/td][td]>5Weeks[/td][td]>5Weeks[/td][td]>5Weeks[/td][td]>5Weeks[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

6666​

[/td][td]snack[/td][td]<2Weeks[/td][td]<1Week[/td][td]<1Week[/td][td]<1Week[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

9876​

[/td][td]Crisps[/td][td]>5Weeks[/td][td]<5Weeks[/td][td]>5Weeks[/td][td]>5Weeks[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

8546​

[/td][td]snack[/td][td]<4Weeks[/td][td]<3Weeks[/td][td]<4Weeks[/td][td]<3Weeks[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

7845​

[/td][td]Drink[/td][td]<1Week[/td][td]<1Week[/td][td]<1Week[/td][td]<2Weeks[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

3265​

[/td][td]Drink[/td][td]<2Weeks[/td][td]<5Weeks[/td][td]<4Weeks[/td][td]<3Weeks[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]Weekly Stock Coverage[/td][td][/td][td][/td][td][/td][td]

6/1/2018​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]Product Description[/td][td]No. Product Codes[/td][td]< Min[/td][td]In Range[/td][td]> Max[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

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

3​

[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

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

3​

[/td][td]

1​

[/td][td]

2​

[/td][td]

0​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

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

3​

[/td][td]

2​

[/td][td]

0​

[/td][td]

1​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

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

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td]<2weeks<="" td="">[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td]In Range <3Weeks and <4Weeks[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td]>Max <5Weeks and >5Weeks[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet43 (2)[/td][/tr][/table]

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"))