The formula is working brilliantly....thankyou everyone. I appreciate the
time you've all spent on this.



"Roger Govier" wrote:

Very nice.
Both work admirably, and I agree far better to avoid array entries.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
Since we are dealing with fixed length strings (2), we don't need the
LEN(
$IU$1:$IU$50 ) at all:

=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER(
$IU$1:$IU$50
), "" ) ) ) / 2, $IV$1:$IV$50 )

I posted the other one to make it a bit more generic.
Lookup tables should be a named range with no empty rows, so the
#DIV/0
error is bothering me a lot less than having to use an array
formula...

In any case, this one should work OK in most situations:

=SUMPRODUCT( ( LEN( B1) - LEN( SUBSTITUTE( LOWER( B1), LOWER(
$IU$1:$IU$50
), "" ) ) ) / ( LEN( $IU$1:$IU$50 ) + ( LEN( $IU$1:$IU$50 ) = 0 ) ),
$IV$1:$IV$50 )

--
Festina Lente


"Roger Govier" wrote:

Hi

If there are blank values within range IU1:IU50 then I get #DIV/0
errors.

Array entering
{=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 )
, LOWER( $IU$1:$IU$50 ), "" ) ) ) /
MAX(LEN( $IU$1:$IU$50 ),1), $IV$1:$IV$50 )}

seems to give the correct result though.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
If you need to get multiple hits, then this one should do it:
=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER(
$IU$1:$IU$50
), "" ) ) ) / LEN( $IU$1:$IU$50 ), $IV$1:$IV$50 )
--
Festina Lente


"Roger Govier" wrote:

Hi PapaDos

Very clever thinking. I like it!!

However, if there should be a repeat of items in cell B2, then it
would
not get picked up for the second (or subsequent) occurrence. Bob's
formula does pick up multiple occurrences.

I have no idea whether the OP's data has single or multiple
occurrences
of items. I still have not received any file from him / her.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
LOL, it does work.

I simply approach it the other way around.
Instead of looking for extracted parts of the string into a
table,
I
look
for the table column in the string...
--
Festina Lente


"Epinn" wrote:

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

=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) *
$IV$1:$IV$50 )

I am totally lost! Can't see that the formula works.

No file from Terranoman?

Epinn

"PapaDos" wrote in message
...
=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) *
$IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

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.