Reputation: 1053
I have cells that contain text like the following:
10 - (1/2/20) *C <-- Cell E2
10 - (1/3/20) <-- Cell E3
I am trying to add the numbers before the hyphen if it contains an asterisk.
Using the following formula I get the correct result of "10"
=LEFT(E2,FIND("*",E2,1)-13)
(surprisingly I can use " * " instead of " ~* " like I thought I would have to?)
Attempting to put it into an array to calculate multiple scenarios I get a #VALUE!
=SUMPRODUCT(--(LEFT(E2:INDEX(E:E,MATCH("zzz",E:E)),FIND("*",E2:INDEX(E:E,MATCH("zzz",E:E)),1)-13)))
I assume this is because not all my cells will contain the "*", I am just fumbled on getting things to coordinate correctly.
Example:
Upvotes: 0
Views: 536
Reputation: 60174
To return 30
from your data set, try:
=SUMPRODUCT(IF(ISNUMBER(FIND("*",myRange)),--LEFT(myRange,(FIND("-",myRange)-1)),0))
In some earlier versions of Excel, you may need to "confirm" this array-formula by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...}
around the formula as observed in the formula bar
Algorithm
*
, look for the hyphen and return the value precedingSUM
the resultant array.
SUMPRODUCT
instead of SUM
may allow the formula to be normally entered. In recent version of Excel, either may be usable with normal entry of the formula.Upvotes: 1