SUM and Contains Formula

[sx200apul]

Hi,
Newbie need some help,

[TABLE="width: 500"]
<tbody>[TR]
[TD]EXTRAS
[/TD]
[TD]Colour
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 530"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2x25002770, 1xPAD, 1xWindow, 2xInso[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1x25005100, 1x25002770, 1xPAD, 1xMotor, 1xWindow[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 530"]
<tbody>[TR]
[TD]2x46004100, 2xMotor, 1xPAD, 6xInso[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2x30003150, 1x25002770, 3xWindow[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1x25005100, 2x25002770, 1xPAD, 3xInso[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3x25002770, 1x36003150, 2xPAD, 10xWindow, 2xBarnWindow[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2x25002770[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1x25002770, 1xPAD, 1xWindow, 1xInso, 1xLT1.2x3.5x2.595[/TD]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="class: xl65, width: 99"]Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I am trying to total up different sized items in a text string an sum the totals by the size and colour from the table above.

The items to total are "25002770" or "36003150" or "46004100" etc .. these are preceded by the number of each of these items.
the preceding characters specify how many of each.. "2x" = 2 of , "3x" = 3 of
Then in the column "Colour" is the colour of the items detailed this line.

For exmaple: 2x25002770 - "Has 2 roller doors" indicate by 2x as mentioned above.

I am trying to generate the totalled list for these items as shown in the results table below.

Any help gladly appreciated

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]Size & Colour[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25002770 Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25002770 Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25002770 Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25002770 Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25002770 Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]SUB-TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25005100 Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25005100 Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25005100 Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25005100 Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]25005100 Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]SUB-TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]30003150 Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]30003150 Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]30003150 Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]30003150 Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]30003150 Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]SUB-TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]36003150 Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]36003150 Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]36003150 Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]36003150 Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]36003150 Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]SUB-TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]42003150 Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]42003150 Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]42003150 Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]42003150 Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]42003150 Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]SUB-TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]46004100 Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]46004100 Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]46004100 Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]46004100 Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]46004100 Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]SUB-TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]50005100 Heritage Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]50005100 Mist Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]50005100 Mountain Blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]50005100 Slate Grey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]50005100 Smooth Cream[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl65, width: 179"]SUB-TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Are you the same person as this poster (Lukums): https://www.mrexcel.com/forum/excel-questions/1071055-basic-search-isnumber-len-formula-help.html You seem to be posting from the same location.

If so, why do you have two accounts and why have you posted essentially the same question twice?

 

[sx200apul]

Hi Rory,
Thanks for responding, no, he's not the same person. I'm new to this and he is trying to help me get the query out as well.
Thanks
Paul