I managed it with a formula that basically looks like this assuming the data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(
IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),
MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to split out

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a Refersto
value of

=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each
row)

Each abbreviation will have it's own unique number (weight) allocated in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc
in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.