Help using vlookup for multiple instances of the identifier

[Guava]

Hello,

Below is an example of the data I am working with:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]=LEFT(B2,2)[/TD]
[TD="align: center"]Full Location[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Units[/TD]
[/TR]
[TR]
[TD="align: center"]A0[/TD]
[TD="align: center"]A010101A[/TD]
[TD="align: center"]74517894[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]D0[/TD]
[TD="align: center"]D010102A[/TD]
[TD="align: center"]74517894[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]GD[/TD]
[TD="align: center"]GD10103A[/TD]
[TD="align: center"]96441511[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]RE[/TD]
[TD="align: center"]RE10101A[/TD]
[TD="align: center"]74517894[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]RE[/TD]
[TD="align: center"]RE10102A[/TD]
[TD="align: center"]87456430[/TD]
[TD="align: center"]144[/TD]
[/TR]
[TR]
[TD="align: center"]RE[/TD]
[TD="align: center"]RE10103A[/TD]
[TD="align: center"]74517894[/TD]
[TD="align: center"]100[/TD]
[/TR]
</tbody>[/TABLE]

Here is the output I have in mind:


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]INPUT ID HERE[/TD]
[TD="align: center"]Total units in RE[/TD]
[TD="align: center"]Total units in other[/TD]
[/TR]
[TR]
[TD="align: center"]74517894[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]110[/TD]
[/TR]
</tbody>[/TABLE]

I've been able to use vlookup to display one row of data, however it gets very complicated very quickly when I try to incorporate summing of multiple vlookups.
And even more so when I try to separate 'RE' units from the other units.

Would it be easier to create two tables first in order to separate 'RE' locations from 'other locations'?

Any pointers in the right direction would really help.

Thank you.

 

[sheetspread]

Excel 2010
	A	B	C	D
1	Prefix	Full Location	ID	Units
2	A0	A010101A	74517894	10
3	D0	D010102A	74517894	100
4	GD	GD10103A	96441511	50
5	RE	RE10101A	74517894	100
6	RE	RE10102A	87456430	144
7	RE	RE10103A	74517894	100
8
9
10	INPUT ID HERE	Total units in RE	Total units in other
11	74517894	200	110
Sheet4

Cell Formulas
Range	Formula
B11	=SUMIFS($D$2:$D$7,$A$2:$A$7,"RE",$C$2:$C$7,A11)
C11	=SUMIF($C$2:$C$7,A11,$D$2:$D$7)-B11

 

[markmzz]

Another way (without use the LEFT column) in G2 and H2:

=SUMPRODUCT(--($C$2:$C$7=$F2),--(LEFT($B$2:$B$7,2)="RE"),$D$2:$D$7)

=SUMPRODUCT(--($C$2:$C$7=$F2),$D$2:$D$7)-G2

[TABLE="class: grid, width: 702"]
<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]
[/TR]
[TR]
[TD]1[/TD]
[TD]=LEFT(B2,2)[/TD]
[TD]Full Location[/TD]
[TD]ID[/TD]
[TD]Units[/TD]
[TD][/TD]
[TD]INPUT ID HERE[/TD]
[TD]Total units in RE[/TD]
[TD]Total units in other[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A0[/TD]
[TD]A010101A[/TD]
[TD]74517894[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]74517894[/TD]
[TD]200[/TD]
[TD]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]D0[/TD]
[TD]D010102A[/TD]
[TD]74517894[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GD[/TD]
[TD]GD10103A[/TD]
[TD]96441511[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]RE[/TD]
[TD]RE10101A[/TD]
[TD]74517894[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]RE[/TD]
[TD]RE10102A[/TD]
[TD]87456430[/TD]
[TD]144[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]RE[/TD]
[TD]RE10103A[/TD]
[TD]74517894[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/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]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[Guava]

You guys made it seem so simple. Thank you so much! Problem solved.