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
06.01.01.01.01
06.01.01.01.02
06.01.01.02.01
06.01.01.03.01
06.01.02.01.01
06.01.02.03.02
06.01.01.01
06.01.02.01
06.01.01.03
06.01.02.03

<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>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]6679[/TD]
[TD="align: right"]3119[/TD]
[TD="align: right"]8296[/TD]
[TD="align: right"]9416[/TD]
[TD="align: right"]3868[/TD]
[TD="align: right"]8804[/TD]
[TD="align: right"]8505[/TD]
[TD="align: right"]6064[/TD]
[TD="align: right"]5217[/TD]
[TD="align: right"]5181[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]7862[/TD]
[TD="align: right"]8942[/TD]
[TD="align: right"]2136[/TD]
[TD="align: right"]9698[/TD]
[TD="align: right"]5524[/TD]
[TD="align: right"]3625[/TD]
[TD="align: right"]4348[/TD]
[TD="align: right"]3463[/TD]
[TD="align: right"]6008[/TD]
[TD="align: right"]7800[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]2756[/TD]
[TD="align: right"]8350[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]6457[/TD]
[TD="align: right"]9622[/TD]
[TD="align: right"]8751[/TD]
[TD="align: right"]8404[/TD]
[TD="align: right"]5533[/TD]
[TD="align: right"]1947[/TD]
[TD="align: right"]6035[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]7992[/TD]
[TD="align: right"]8919[/TD]
[TD="align: right"]8552[/TD]
[TD="align: right"]4697[/TD]
[TD="align: right"]3154[/TD]
[TD="align: right"]5892[/TD]
[TD="align: right"]7218[/TD]
[TD="align: right"]4550[/TD]
[TD="align: right"]9459[/TD]
[TD="align: right"]4200[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]7762[/TD]
[TD="align: right"]7512[/TD]
[TD="align: right"]1972[/TD]
[TD="align: right"]6681[/TD]
[TD="align: right"]6955[/TD]
[TD="align: right"]8577[/TD]
[TD="align: right"]3399[/TD]
[TD="align: right"]7734[/TD]
[TD="align: right"]9245[/TD]
[TD="align: right"]1967[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]7619[/TD]
[TD="align: right"]7299[/TD]
[TD="align: right"]3275[/TD]
[TD="align: right"]9923[/TD]
[TD="align: right"]3634[/TD]
[TD="align: right"]4746[/TD]
[TD="align: right"]7213[/TD]
[TD="align: right"]2407[/TD]
[TD="align: right"]2761[/TD]
[TD="align: right"]9656[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

</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)