Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions | |||
How to auto-fill text based on text in another cell | Excel Discussion (Misc queries) | |||
Counting an Array based on a calculation | Excel Discussion (Misc queries) | |||
Can I count in an array based on a function? | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |