Brillian. That works great. Thanks.

Ade

"N Harkawat" wrote:

=SUMPRODUCT(--(ISNUMBER(FIND("/",A1:A3))),--(ISNUMBER(FIND(A5,A1:A3))),(B1:B3))*0.5+SUMIF(A1:A 3,A5,B1:B3)

assuming that your data is from A1:B3 and the value looked up is on cell A5



"RestlessAde" wrote in message
...
Hi,

I have the following columns I would like to create an automatic formula
to
sum:

Person Sales
AL/AS 30.0
AL 25.0
AS 20.0

The formula should detect a person's initials (e.g. AL or AS in this
example) and only sum if they are either wholly or partly involved with
the
sales. Where they are jointly allocated (e.g. AL/AS) only half of the
sales
revenue should be added.

So the correct formulas would return:
AL 40.0
AS 35.0

I was trying to use the sumproduct function with an embedded Left() and
Right() functions. However the left/right functions do not work on an
array
of text. I could make this work if i repeated the formula over and again
for
each cell I want to include in the array, but this seems like hard work.

Any thoughts?
RA