# Looking up part of a cell

#### scouse

##### Board Regular
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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How do you get to 5, 500 ?

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

scouse

Something like this? The formula in F2 is:
=SUMIF(A:A,D2&E2,B:B)
Mr Excel.xls
ABCDEF
1A12343000ProgramDistrictTotal
2A1235200A12345500
3B12345000
4A12342500
5
Sum Region

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?

scouse

What about using a PivotTable then like this.

1. Insert two new columns between column A and column B.
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
ABCDEFGHIJK
1Option/RegionOptionRegionAmountSum of AmountOption
2A1234A12343000RegionAB(blank)Grand Total
3A1235A123520012345500700012500
4B1234B12345000123542004200
5A1234A12342500158940004000
6B1234B12342000(blank)
7A1235A12351000Grand Total97001100020700
8A1235A12353000
9B1589B15894000
10
Sum Region (2)

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

Scouse

Replies
5
Views
194
Replies
4
Views
83
Replies
0
Views
373
Replies
6
Views
934
Replies
4
Views
189

1,196,517
Messages
6,015,682
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back