Summing an array based on text condition
 

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

No offense but you should avoid a layout like that, if there are only 2
people (AL and AS) you could use

=SUMPRODUCT(--(ISNUMBER(FIND("AL",A2:A4))),B2:B4)-(SUMPRODUCT(--(A2:A4="AL/AS"),B2:B4)/2)

for AL

Much better if you use only one entry in the person range and split the
amounts, thus you would have

AL 15
AL 25
AS 15
AS 20

then you could use

=SUMPRODUCT(--(A2:A5="AL"),B2:B5)

--
Regards,

Peo Sjoblom


"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

=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

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