Looking up part of a cell

[scouse]

I have a code
A1234
A=programme
123= Region
1234=district (cos i could have many different 4 for a region)

If i want to lookup the information by district what formula could i use to look up the 1234 only for programme A and sum the total how could i do it? (the answer i would want here is €5500)

Col A Col B
A1234 €3000
A1235 €200
B1234 €5000
A1234 €2500

regards,
Steve

 

[SteveO59L]

How do you get to 5, 500 ?

 

[scouse]

both examples of a1234 add to €5500 (the B1234 shouldnt be summed)

 

[Peter_SSs]

scouse

Something like this? The formula in F2 is:
=SUMIF(A:A,D2&E2,B:B)

Mr Excel.xls
	A	B	C	D	E	F
1	A1234	3000		Program	District	Total
2	A1235	200		A	1234	5500
3	B1234	5000
4	A1234	2500
5
Sum Region

 

[scouse]

Peter,

thanks for that. The only problem is that in the long term i have A-O as options as well as about 160 different districts.... would i have to do that for every option?

 

[Peter_SSs]

scouse

What about using a PivotTable then like this.

1. Insert two new columns between column A and column B.
2. Insert a new row above the data and add headings.
3. In B2 (copied down): =LEFT(A2,1)
4. In C2 (copied down): =SUBSTITUTE(A2,B2,"")
5. Select columns B:D and then Data|PivotTable...

I won't go through all the remaining steps unless needed, but you should be able to come up with something like this, which hopefully is useful to you.

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Option/Region	Option	Region	Amount		Sum of Amount	Option
2	A1234	A	1234	3000		Region	A	B	(blank)	Grand Total
3	A1235	A	1235	200		1234	5500	7000		12500
4	B1234	B	1234	5000		1235	4200			4200
5	A1234	A	1234	2500		1589		4000		4000
6	B1234	B	1234	2000		(blank)
7	A1235	A	1235	1000		Grand Total	9700	11000		20700
8	A1235	A	1235	3000
9	B1589	B	1589	4000
10
Sum Region (2)

 

[scouse]

Peter,
looks excellent - will have to investigate further. I am in treading water territory water i am afraid..!

Scouse