| Home |
| Search |
| Today's Posts |
|
#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 |
| 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) | |||
Threaded Mode
