In this case Roger, I think it needs a BIG re-design. I tried to concoct a
formula where the values were not comma separated in one cell, but many
cells, and it wasn't much better.

This took me a while to figure out, I wouldn't want to come to this
spreadsheet and see that monster unprepared, so I think the maintenance is
probably not worth it.

Start again with the requirement, and come up with a simpler design. Often
works for me.

--
HTH

Bob Phillips

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

"Roger Govier" wrote in message
...
Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

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

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
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.