Adding multiple values in index match

[AAM985]

Good morning,

I'm working on file that will show me the total requirement for a specific part on a specific day. Unfortunately there are multiple line items per part on a day. I need to find a way to return the total value on a specific day.

I started with the below formula, but it only returns the first value.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>={INDEX($I$3:$I$14,MATCH(1,(A3=$H$3:$H$14)*(B$1=$J$3:$J$14),0))}

I then tried this formula, but it returned a error.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>
={SUMPRODUCT(--($H$3:$H$14&$J$3:$J$14=$A3&$B$1),INDEX($I$3:$I$14,MATCH(1,(A3=$H$3:$H$14)*(B$1=$J$3:$J$14),0)))}

Can someone help me with this formula, it's driving me crazy?!

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {border:.5pt solid windowtext;}.xl64 {mso-number-format:"Short Date"; border:.5pt solid windowtext;}.xl65 {color:black; mso-number-format:"Short Date"; border:.5pt solid windowtext;}.xl66 {border:.5pt solid windowtext; background:yellow; mso-pattern:black none;}--></style>

	A	B	C	D	E	F	G	H	I	J
2		04-01-18	04-02-18	04-03-18
3	Part	req	req	req
4	F100	#VALUE!						F100	5	04-01-18
5	F200							F100	19	04-01-18
6	F300							F100	4	04-01-18
7	F400							F100	1	04-02-18
8	F500							F200	3	04-01-18
9								F200	56	04-01-18
10								F200	65	04-02-18
11								F200	34	04-02-18
12								F300	23	04-01-18
13								F300	11	04-01-18
14								F300	22	04-02-18
15								F300	14	04-02-18
16

<!--StartFragment--> <colgroup><col width="65" span="11" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"Short Date";}.xl64 {color:black; mso-number-format:"Short Date";}--></style>

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"Short Date";}.xl64 {color:black; mso-number-format:"Short Date";}--></style>

<colgroup><col width="65" span="10" style="width:65pt"></colgroup><tbody>
</tbody>

 

[AlanY]

try this in B4, copy down and across

=SUMIFS($I$4:$I$15,$H$4:$H$15,$A4,$J$4:$J$15,B$2)

 

[AAM985]

It's returning zero's.

Should i just use the sumifs or replace the sumproduct with the sumifs?

 

[markmzz]

Hi!

Try this too in B4 and copy down and to the right:

=SUMPRODUCT(--($H$4:$H$15=$A4),--($J$4:$J$15=B$2),$I$4:$I$15)

	A	B	C	D	E	F	G	H	I	J	K
1
2		01/04/2018	02/04/2018	03/04/2018
3	Part	req	req	req
4	F100	28	1	0				F100	5	01/04/2018
5	F200	59	99	0				F100	19	01/04/2018
6	F300	34	36	0				F100	4	01/04/2018
7	F400	0	0	0				F100	1	02/04/2018
8	F500	0	0	0				F200	3	01/04/2018
9								F200	56	01/04/2018
10								F200	65	02/04/2018
11								F200	34	02/04/2018
12								F300	23	01/04/2018
13								F300	11	01/04/2018
14								F300	22	02/04/2018
15								F300	14	02/04/2018
16
***	*****	**********	**********	**********	**	**	**	*****	****	**********	**

<tbody>
</tbody>

Markmzz

 

[AlanY]

It's returning zero's.

Should i just use the sumifs or replace the sumproduct with the sumifs?

it works for me

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1
2		04/01/2018	04/02/2018	04/03/2018
3	Part	req	req	req
4	F100	28	1	0				F100	5	04/01/2018
5	F200	59	99	0				F100	19	04/01/2018
6	F300	34	36	0				F100	4	04/01/2018
7	F400	0	0	0				F100	1	04/02/2018
8	F500	0	0	0				F200	3	04/01/2018
9								F200	56	04/01/2018
10								F200	65	04/02/2018
11								F200	34	04/02/2018
12								F300	23	04/01/2018
13								F300	11	04/01/2018
14								F300	22	04/02/2018
15								F300	14	04/02/2018
Sheet1

Cell Formulas
Range	Formula
B4	=SUMIFS($I$4:$I$15,$H$4:$H$15,$A4,$J$4:$J$15,B$2)