Hi

As I said earlier, you can mail the file to me if you wish.
remove NOSPAM from my address to mail direct i.e roger at technology4u
dot co dot uk
Do the obvious with the spaces and dots.

I will be pleased to take a look and post back.

--
Regards

Roger Govier


"Terranoman" wrote in message
...
Unfortunately I've inherited the spreadsheet and attempting to make it
work
(without the use of a calculator and pen). Is it possible that I
could
forward it on for perusal? You guys are way beyond my excel
knowledge.

"Roger Govier" wrote:

not U and I

should have read
not U and V

--
Regards

Roger Govier


"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.