Reputation: 3

Getting the sum of a specific number in a cell

I just want to get the sum of all the values on the left side and not include the date.

Upvotes: 0

Answers (5)

Reputation: 461

Hey, Try this, even the data is inconsistent with more spaces, this will work.

=SUMPRODUCT(LEFT(A2:A6,SEARCH("-",SUBSTITUTE(A2:A6," ",""))-1)+0)

Upvotes: 0

Reputation: 461

Textsplit, Textbefore functions are available on office365. If the user is not using office365, sumproduct would be the preferrable choice.

Upvotes: 0

Reputation: 848

A slight variation to the previous proposals for two reasons:

Don’t need to invoke SUMPRODUCT(). If - - has been used to force enumeration, then SUM() is sufficient.
While TEXTSPLIT() works, you’ll run into problems if your column length is one. TEXTSPLIT will create a horizontal array with multiple values if applied to a range with only one row, producing ambiguity and a potential error.

Thus:

=SUM(--TEXTBEFORE(A2:A6," "))

is more versatile.

Upvotes: 0

Reputation: 76000

What about:

=SUM(--TEXTSPLIT(A1:A4," "))

Upvotes: 1

Reputation: 37155

Try SUMPRODUCT(). Assuming, you have space after value.

=SUMPRODUCT(--LEFT(A1:A4,SEARCH(" ",A1:A4)))

Upvotes: 1