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>
[TABLE="width: 715"]
<!--StartFragment--> <colgroup><col width="65" span="11" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 65"] [/TD]
[TD="class: xl66, width: 65"]A[/TD]
[TD="class: xl66, width: 65"]B[/TD]
[TD="class: xl66, width: 65"]C[/TD]
[TD="class: xl66, width: 65"]D[/TD]
[TD="class: xl66, width: 65"]E[/TD]
[TD="class: xl66, width: 65"]F[/TD]
[TD="class: xl66, width: 65"]G[/TD]
[TD="class: xl66, width: 65"]H[/TD]
[TD="class: xl66, width: 65"]I[/TD]
[TD="class: xl66, width: 65"]J[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64, align: right"]04-01-18[/TD]
[TD="class: xl64, align: right"]04-02-18[/TD]
[TD="class: xl64, align: right"]04-03-18[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl63"]Part[/TD]
[TD="class: xl63"]req[/TD]
[TD="class: xl63"]req[/TD]
[TD="class: xl63"]req[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl63"]F100[/TD]
[TD="class: xl63, align: center"] #VALUE![/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F100[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl64, align: right"]04-01-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]5[/TD]
[TD="class: xl63"]F200[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F100[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl64, align: right"]04-01-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]6[/TD]
[TD="class: xl63"]F300[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F100[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl64, align: right"]04-01-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]7[/TD]
[TD="class: xl63"]F400[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F100[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl64, align: right"]04-02-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]8[/TD]
[TD="class: xl63"]F500[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F200[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl64, align: right"]04-01-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]9[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F200[/TD]
[TD="class: xl63, align: right"]56[/TD]
[TD="class: xl64, align: right"]04-01-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]10[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F200[/TD]
[TD="class: xl63, align: right"]65[/TD]
[TD="class: xl64, align: right"]04-02-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]11[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F200[/TD]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl64, align: right"]04-02-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F300[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl65, align: right"]04-01-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]13[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F300[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl65, align: right"]04-01-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]14[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F300[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl64, align: right"]04-02-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]15[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]F300[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl64, align: right"]04-02-18[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]16[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


<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>
[TABLE="width: 650"]
<colgroup><col width="65" span="10" style="width:65pt"></colgroup><tbody> [TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: grid, width: 539"]
<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]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD="align: right"]03/04/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Part[/TD]
[TD]req[/TD]
[TD]req[/TD]
[TD]req[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]F100[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]F200[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F100[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]F300[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F100[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]F400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]F500[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F200[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F200[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F200[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F300[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F300[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F300[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F300[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]02/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*****[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]*****[/TD]
[TD]****[/TD]
[TD]**********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

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)