2D array lookup that can return multiple values

[andrewhorne252]

Hello,

I have a table of die numbers that correspond to another table of production numbers. I am looking to see if it is possible to automate the summing of the production numbers based on where the die numbers are. I will continue to use this in the future and don't want to have to sum the numbers up manually every time I update the table.

I have looked at multiple examples, but can't find anything like this. I have been trying to find an function that would return an array of numbers, maybe 1's and 0's, that give the location of the die number I am looking for. Then I could use that array to pull the corresponding production numbers.

For example: If i wanted to find the total for die 1E. I would add all of the production numbers in red. I would get 646,326.

I don't know any formula/formula combinations that can pull multiple values out of a 2D array.

Any suggestions? Anything would be greatly appreciated.

Die Positions	11/20/2015	1/21/2016	4/14/2016	4/25/2016	6/20/2016	8/24/2016	10/27/2016	1/20/2017	3/2/2017	5/9/2017	→ → →
1	10012	110513	9551	71723	91425	85557	99993	48888	72516	57167	This
2	10184	110869	9577	71449	91436	85882	99143	49040	72582	57209	will
3	10159	111117	9756	71268	91148	86392	99892	49031	73507	57270	expand
4	10107	111464	9675	71417	91108	85226	100119	49452	73577	57448	outward
5	10207	110921	9593	71086	90946	85690	99919	48666	72236	56662	with each
6	10216	111293	9666	71218	90926	85209	99556	48529	72195	57159	die
7	10115	110523	9613	70188	90992	85794	99854	48921	72340	57477	change
8	10225	110877	9836	71394	90797	85214	99250	49112	72419	57270	→ → →

<tbody>
</tbody>

<table class="wysiwyg_dashes wysiwyg_cms_table_grid" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><tbody>[TR="class: grid"]
[TD]Die[/TD]
[TD="colspan: 2"]Total Produced / die[/TD]
[/TR]
[TR="class: grid"]
[TD]1E[/TD]
[TD="colspan: 2"]646,326[/TD]
[/TR]
[TR="class: grid"]
[TD]2E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]3E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]5E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]6E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]7E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]8E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]9E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]1J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]2J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]3J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]5J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]6J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]7J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]8J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]5F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]6F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]7F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]8F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]1G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]2G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]3G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4C[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]2F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody></table>
Here is a link to an image of the data if the data above does not turn out: Imgur: The most awesome images on the Internet

Right now I am going in and summing up the cells by clicking on them.

I am using excel 2010 on windows.

Thank you,
Andrew

 

[andrewhorne252]

Here is the data again:

Die Positions	11/20/2015	1/21/2016	4/14/2016	4/25/2016	6/20/2016	8/24/2016	10/27/2016	1/20/2017	3/2/2017	5/9/2017	→ → →
1	6E	1E	1E	1E	1E	1E	1E	1G	2E	5J	This
2	1G	3E	3E	3E	3E	3E	3E	3E	3E	3E	will
3	2G	6E	6E	6E	6E	6E	6E	4J	2G	2G	expand
4	8	7E	7E	3G	5F	5F	8F	5F	8F	6F	outward
5	3E	1G	1G	7F	1G	1G	1G	1E	1E	1E	with each
6	4F	4F	5E	5E	5E	5E	5E	5E	5E	4C	die
7	5F	5F	5F	7E	7E	7E	7E	7E	7E	4E	change
8	7F	7F	1P	1P	1P	4J	4J	6E	6E	8E	→ → →

<tbody>
</tbody>

Die Positions	11/20/2015	1/21/2016	4/14/2016	4/25/2016	6/20/2016	8/24/2016	10/27/2016	1/20/2017	3/2/2017	5/9/2017	→ → →
1	10012	110513	9551	71723	91425	85557	99993	48888	72516	57167	This
2	10184	110869	9577	71449	91436	85882	99143	49040	72582	57209	will
3	10159	111117	9756	71268	91148	86392	99892	49031	73507	57270	expand
4	10107	111464	9675	71417	91108	85226	100119	49452	73577	57448	outward
5	10207	110921	9593	71086	90946	85690	99919	48666	72236	56662	with each
6	10216	111293	9666	71218	90926	85209	99556	48529	72195	57159	die
7	10115	110523	9613	70188	90992	85794	99854	48921	72340	57477	change
8	10225	110877	9836	71394	90797	85214	99250	49112	72419	57270	→ → →

<tbody>
</tbody>

Die	Total Produced / die
1E
2E
3E
4E
5E
6E
7E
8E
9E
1J
2J
3J
4J
5J
6J
7J
8J
4F
5F
6F
7F
8F
1G
2G
3G
4C
2F

<tbody>
</tbody>

Thanks,
Andrew

 

[AhoyNC]

Try SUMPRODUCT function.

Excel Workbook
	A	B	C	D	E
1
2		1e	6t	8i	1e
3		i8	6y	1e	g7
4		8i	p0	8i	1e
5
6		1	2	3	4
7		5	6	7	8
8		9	10	11	12
9
10	item	sum
11	1e	24
12	8i	23
Sheet

 

[andrewhorne252]

That works perfectly.

Thank you so much,
Andrew

 

[AhoyNC]

You're welcome. Thanks for the feedback.