Kevin P.
Kevin P.

Reputation: 1053

How to sumproduct of numbers before string if cell contains specific text

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:

enter image description here

Upvotes: 0

Views: 536

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

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

enter image description here

Algorithm

  • If there is a *, look for the hyphen and return the value preceding
  • If there is no asterisk, return a zero.
  • SUM the resultant array.
    • In earlier versions of Excel, using 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

Related Questions