Masking numbers

[Ben51]

Hi All,

I have the following problem...

I have many numbers in the following format associated with several costs:

A B C

06.01.01.01.01 £8000 £40000
06.01.01.01.02 £2000 £50000
06.01.01.02.01 £9000 £60000
06.01.01.03.01 £7000 £70000
06.01.02.01.01 £5000 £40000
06.01.02.01.02 £1000 £30000

I wish to be able to add up all the costs associated with codes beginning with 06.01.01.01
and
06.01.02
etc

Thanks Guys and Girls

Ben

 

[sheetspread]

A formula isn't difficult but you can split the last .xx into a new cell with text to columns fixed width, then have a pivot table or the subtotaler add up the groups.

 

[Ben51]

Unfortunately i am unable to edit column a containing the numbers, could you advise the formula you speak of please?

 

[sheetspread]

Change the parsing point as you like:

Excel 2010
	A	B	C
1	06.01.01.01.01	8000	40000
2	06.01.01.01.02	2000	50000
3	06.01.01.02.01	9000	60000
4	06.01.01.03.01	7000	70000
5	06.01.02.01.01	5000	40000
6	06.01.02.01.02	1000	30000
7
8
9
10	06.01.01.01	10000	90000
11	06.01.02.01	6000	70000
Sheet12

Cell Formulas
Range	Formula
B10	=SUMPRODUCT((LEFT($A$1:$A$6,11)=$A10)*B$1:B$6)

 

[Rick Rothstein]

Change the parsing point as you like:

Excel 2010
	A	B	C
1	06.01.01.01.01	8000	40000
2	06.01.01.01.02	2000	50000
3	06.01.01.02.01	9000	60000
4	06.01.01.03.01	7000	70000
5	06.01.02.01.01	5000	40000
6	06.01.02.01.02	1000	30000
7
8
9
10	06.01.01.01	10000	90000
11	06.01.02.01	6000	70000
Sheet12

Cell Formulas
Range	Formula
B10	=SUMPRODUCT((LEFT($A$1:$A$6,11)=$A10)*B$1:B$6)

Using your layout, you can also put this simpler formula in cell B10 and copy it across and down...

=SUMIF($A$1:$A$6,$A10&"*",B$1:B$6)

 

[Ben51]

Could i expand on that formula to sum columns B and C (i actually have 10 columns) rather than summing individually?

 

[Rick Rothstein]

Could i expand on that formula to sum columns B and C (i actually have 10 columns) rather than summing individually?

With this layout...

	A	B	C	D	E	F	G	H	I	J	K	L
1	06.01.01.01.01	6679	3119	8296	9416	3868	8804	8505	6064	5217	5181
2	06.01.01.01.02	7862	8942	2136	9698	5524	3625	4348	3463	6008	7800
3	06.01.01.02.01	2756	8350	3300	6457	9622	8751	8404	5533	1947	6035
4	06.01.01.03.01	7992	8919	8552	4697	3154	5892	7218	4550	9459	4200
5	06.01.02.01.01	7762	7512	1972	6681	6955	8577	3399	7734	9245	1967
6	06.01.02.03.02	7619	7299	3275	9923	3634	4746	7213	2407	2761	9656
7
8
9
10	06.01.01.01
11	06.01.02.01
12	06.01.01.03
13	06.01.02.03
14

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 100.8px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Put this formula in cell B10 and copy it down to cell B13...

=SUMPRODUCT((LEFT($A$1:$A$6,11)=$A10)*B$1:K$6)

 

[Sanjeev1976]

Just change the formula as below to suit your requirement :

=SUMPRODUCT((LEFT($A$1:$A$6,11)=$A10)*B$1:C$6) to add columns "B" and "C"

 

[Ben51]

Is there a way to make the 11 bit mask interactive dependent on the length.

SUMPRODUCT((LEFT($A$1:$A$6,11)=$A10)*B$1:C$6)

I've tried using the LEN function

SUMPRODUCT((LEFT($A$1:$A$6,(LEN(A10)))=$A10)*B$1:C$6)

 

[Sanjeev1976]

Yes, you can use the following :

=SUMPRODUCT((LEFT($A$1:$A$6,LEN(A10))=A10)*B$1:C$6)