SUM and Contains Formula

S

sx200apul

New Member
Joined
Sep 17, 2018
Messages
5
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]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Basic Search ISNUMBER/LEN formula help!
Replies
8
Views
1K
Aladin Akyurek
Aladin Akyurek
S
  • Question Question
countifs return values range
Replies
4
Views
700
AlphaFrog
AlphaFrog
W
  • Question Question
Formula to Sum Cells when value changes in another Column
Replies
6
Views
6K
WednesdayC
W
C
  • Question Question
If statement to sum if cell contains certain txt
Replies
2
Views
871
Conell8383
C
V
  • Question Question
Excel Match and Sum Problem
Replies
7
Views
2K
pgc01
P

Forum statistics

Threads
1,224,814
Messages
6,181,127
Members
453,021
Latest member
Justyna P

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top