# Help with subtotalling by part of cell name

#### rogermccarthy

##### New Member
Hi,

I have a long spreadsheet showing deaths by UK postcode sector of which there are 8,411 but want to compare this to another spreadsheet giving census religion by postcode area which is the top level of postcode and of which there are 105 with data.

The sector sheet looks likes this with AL1 1 being the first column and the 34 being the 2010 deaths in the second:

Postcode sector 2010
AL1 1 34
AL1 2 68
AL1 3 36
AL1 4 54
AL1 5 48
AL10 0 94
AL10 8 136
AL10 9 65
AL2 1 50
AL2 2 50
AL2 3 67
AL3 4 59
AL3 5 77
AL3 6 31
AL3 7 39
AL3 8 40
AL4 0 44
AL4 8 48
AL4 9 87
AL5 1 35
AL5 2 32
AL5 3 32
AL5 4 53
AL5 5 53
AL6 0 35
AL6 9 63
AL7 1 45
AL7 2 45
AL7 3 80
AL7 4 100
AL8 6 56
AL8 7 67
AL9 5 27
AL9 6 8
AL9 7 58
B1 1 8
B1 2 13
B1 3 0
B10 0 42
B10 9 93

and so on for 8,000 more rows

The postcode area spreadsheet looks like this with the AL being the area in column 1 and 250,427 being the first column of data giving numbers for each religion in the area

All categories: Religion
AL 250,427
B 1,904,658
BA 434,166
BB 488,917
BD 578,336
BH 551,987

and so on for a total 105 rows

Problem is that the postcode areas are not just the first two digits of the postcode sector as some are just one digit (i.e. AL and B in the example are both areas) so I can't do a simple split at third digit or space.

However all postcode areas start with one or two letters and all postcode districts and sectors each add a number (when you get down to units start re-appearing again my own postcode for instance is TN (area) 6 (district) 2 (sector) and DN (unit)

I can subtotal them one by one within a filter (e.g. by selecting filter using starts with AL) but this means doing 105 separate operations which is a pain

Is there a formula which allows me to generate subtotals for anything in column 2 which starts with a letter in column 1 but fixes the problem of there being both two and one letter postcode areas?

e.g. I just want subtotals for AL and B and BA etc in the example above.

Thanks,

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Replies
12
Views
630
Replies
3
Views
228
Replies
1
Views
124
Replies
3
Views
665
Replies
3
Views
252

1,203,250
Messages
6,054,383
Members
444,721
Latest member
BAFRA77

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