Lookup or other function?

[BT690]

Hi

Trying something new with a large database and was hoping someone might have an idea how to go about it.

I have two sheets of data with and ID code in each sheet but i need to match a sum of all the results into a summary sheet like below

I need to search Column A of sheet 1 for eg "R6417" and return a sum of all the values in column G for "R6417" which in this example sum to 163.11

I need to place this in a column in the summary sheet so you can see the sum of all these parts

A	B	C	D	E	F	G
R6417	251.68	1.50	377.52	21.50	1.02	20.43
R6417	278.90	1.98	552.22	21.50	1.02	19.95
R6417	145.90	1.74	253.87	21.50	1.02	20.19
R6417	269.80	1.58	426.28	21.50	1.02	20.35
R6417	184.36	1.69	311.57	21.50	1.02	20.24
R6417	254.82	1.54	392.42	21.50	1.02	20.39
R6417	217.59	1.25	271.99	21.50	1.02	20.68
R6417	187.69	1.05	197.07	21.50	1.02	20.88
R0387	843.21	1.51	1273.25	14.88	1.02	13.67
R0387	745.12	1.69	1259.25	14.88	1.02	13.49
R0387	654.87	1.55	1015.05	14.88	1.02	13.63
R0387	851.43	1.84	1566.63	14.88	1.02	13.34
R0387	968.29	1.37	1326.56	14.88	1.02	13.81
R0387	886.32	1.29	1143.35	14.88	1.02	13.89
R0387	761.62	1.41	1073.88	14.88	1.02	13.77
R0387	954.67	1.06	1011.95	14.88	1.02	14.12
R0387	639.28	1.80	1150.70	14.88	1.02	13.38
R0129	453.91	1.49	676.33	52.12	1.02	51.67
R0129	482.33	1.44	694.56	52.12	1.02	51.72
R0129	501.84	1.67	838.07	52.12	1.02	51.49
R0129	491.56	1.28	629.20	52.12	1.02	51.88
R0129	533.64	1.61	859.16	52.12	1.02	51.55
R0129	511.95	1.93	988.06	52.12	1.02	51.23
R0129	499.58	1.76	879.26	52.12	1.02	51.40
R0501	102.94	1.08	111.18	74.80	1.02	75.22
R0501	110.67	1.94	214.70	74.80	1.02	74.36
R0501	130.86	1.80	235.55	74.80	1.02	74.50
R0501	102.51	1.25	128.14	74.80	1.02	75.05
R0501	118.73	1.47	174.53	74.80	1.02	74.83

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

I tried VLOOKUP to return a value but it doesn't sum the values =VLOOKUP(A1,Sheet1!A1:G2562,7,TRUE)

Any ideas how to insert a sum in this command or alternate method?

Thanks in advance

 

[Anand Sharma]

Hi,

In this case you can use sumif formula to match IDs and sum corresponding value

=SUMIF($A$2:$A$30,A2,$G$2:$G$30)

 

[BT690]

Hi Anand

Unfortunately it didn't give me the desired result sorry. Maybe I am just not understanding the command and string though? I have tried all the help in excel without any luck. Quite frustrating for a basic excel user I must admit!

What i need is to look at the code in sheet 1 cell A2 (EG R0501) and look in all of column A of sheet 2 for the same code (R0501) then return the value of that row in Cell G (eg 74.83). There will be multiple repeat entries of the same code in Sheet 1 and only one corresponding code in Sheet 2. Its the value of cell G in sheet 2 i am interested in.

Thanks,

 

[AliGW]

Is this what you are looking for?

=INDEX(Sheet2!$G$1:$G$100,MATCH(A2,Sheet2!$A$1:$A$100,0),0)

Change the ranges to suit.

 

[Aladin Akyurek]

Hi

Trying something new with a large database and was hoping someone might have an idea how to go about it.

I have two sheets of data with and ID code in each sheet but i need to match a sum of all the results into a summary sheet like below

I need to search Column A of sheet 1 for eg "R6417" and return a sum of all the values in column G for "R6417" which in this example sum to 163.11

I need to place this in a column in the summary sheet so you can see the sum of all these parts

I tried VLOOKUP to return a value but it doesn't sum the values =VLOOKUP(A1,Sheet1!A1:G2562,7,TRUE)

Any ideas how to insert a sum in this command or alternate method?

Thanks in advance

As already suggested...

=SUMIFS(Sheet1!G:G,Sheet1!A:A,$A1)

If you don't have SUMIFS, try:

=SUMIF(Sheet1!A:A,$A1,Sheet1!G:G)

these formulas sums all values which are associated with $A1 of the summary sheet you appear to have.

 

[Anand Sharma]

Use sumif formula here

It will lookup the value in field and provide you the sum of matching criteria

=SUMIF($A$2:$A$9,"="&A2,$G$2:$G$13)

 

[BT690]

Hi AliGW

No doesn't work either sorry. I must not be explaining my self well or something.

I spent most of yesterday copy & paste values to make it work this time but next week, month or whenever new data is returned that process needs to be redone. Must be a simple way of using excel but as a pretty basic user that stays within safe bounds using match, index, lookups etc its down right confusing.

Sheet 1

Code	L1	W1	A1
R687	152	6	912.00
R42	157	6.5	1020.50
R571	519	7	3633.00
R613	211	7	1477.00
R128	561	5	2805.00
R362	424	5.5	2332.00
R632	310	6.5	2015.00
R768	264	6	1584.00
R324	61	8	488.00
R764	57	6	342.00
R658	368	4.5	1656.00
R439	205	6	1230.00
R589	199	7	1393.00
R14	80	6	480.00
R219	131	8	1048.00
R0219a	108	8	864.00
R0078b	96	8	768.00
R78	160	8	1280.00
R146	139	6.2	861.80
R243	534	6	3204.00
R244	815	6	4890.00
R301	122	7	854.00
R0301a	98	7	686.00
R387	274	7.6	2082.40
R5	249	7	1743.00
R129	31	5.5	170.50
R491	118	6.5	767.00
R70	249	4.5	1120.50

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2

Code L1 W1 D1 PR LG A1 R687 1 0.5 0.5 1 152 R687 1.6 1 1.6 1 152 R687 10 3.3 33 1 152 R687 3.5 3 10.5 1 152 R687 1 1 1 1 152 R687 6.6 1 6.6 1 152 R687 60 0.5 30 2 152 R687 2 0.5 1 2 152 R687 16 0.5 8 2 152 R687 6 1.5 9 1 152 R687 4 0.5 2 2 152 R687 6 0.5 3 2 152 R687 27.5 1 27.5 1 152 R571 4 1 4 1 519 R571 1 1 1 1 519 R42 38 0.5 19 2 157 R42 0 0 0 1 157 R571 1.2 0.7 0.84 1 519 R571 7.1 1.8 12.78 3 519 R571 20 0 0 1 519 R571 8 2.7 21.6 1 519 R571 5.6 1.6 8.96 1 519 R571 20 1 20 1 519 R571 2 2 4 1 519 R571 0 0 0 1 519 R571 4 2.1 8.4 1 519 R571 7 1.2 8.4 1 519 R571 7 2 14 1 519 R571 8.8 2.4 21.12 1 519 R571 7.5 2 15 1 519 R571 8 0 0 1 519 R571 3.5 2.1 7.35 1 519 R571 5.6 4.4 24.64 1 519 R571 7.7 0 0 1 519 R571 30.4 6.7 203.68 1 519 <colgroup><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

How do i search sheet 1 Code column for R687 and return the corresponding value of A1 in sheet 1 (eg 912) and insert that into sheet 2 A1 column for each matching code entry?

For example in the above two data sheets i want to get the 912 from sheet 1 and place in sheet 2 A1 column where the two codes match.

Sorry but learning excel functions is like learning a new language.

thanks

 

[Aladin Akyurek]

The following was posted just before the recent board trouble. It appears it not make to the board database. Here once again:

Let A:D of Sheet1 house the upper exhibit of post #7.
Let A:G of Sheet2 house the lower exhibit of post #7.

In D2 of Sheet2 enter:

=VLOOKUP($A2,Sheet1!A:D,MATCH(G$1,INDEX(Sheet1!A:D,1,0),0),0)