Let's say I have a spreadsheet look like this.

A |B
1 |1.0 2016-05-07
2 |0.5 2016-04-12
3 |x.y yyyy-MM-dd

How can I sum those first values(1.0, 0.5)?

  • Are the numbers all formatted as #.# followed by space? Are you trying to get the sum in a cell value with a formula or use VBA macro? – dbmitch Jun 29 '16 at 5:53
  • @dbmitch Yes all values are #.#. And I want to know how to =SUM(...). – Jin Kwon Jun 29 '16 at 5:54
  • I assume you can't spilt them into different cells? – Dave Jun 29 '16 at 5:56
  • @Dave I wish I can. – Jin Kwon Jun 29 '16 at 5:57
  • In that case use left and sum... support.office.com/en-us/article/… – Dave Jun 29 '16 at 5:59

You can use array formula like the following:

=SUMPRODUCT(1*LEFT(A1:A3,FIND(" ",A1,1)-1))
where A1:A3 is the column of your data, and you have always a space between numbers and date, the formula finds the first space and read the number on the left and use Ctrl + Shift + Enter instead of 'Enter' for array formula, sumproduct will add the numbers.
In case your Data does not start always with numbers you can use:
=SUMPRODUCT(IF(ISNUMBER(VALUE(LEFT(A1:A3,FIND(" ",A1,1)-1))),1*LEFT(A1:A3,FIND(" ",A1,1)-1),0))


EDIT - MY MISTAKE - YOU CAN DO IT WITH a basic Excel functions - see answer!

Here's another way using a VBA module, and custom public function

Insert this in VBA Code

Public Function SumLeftData(rgeData As Range) As Double

    Dim celVal  As Object

    For Each celVal In rgeData.Cells
        SumLeftData = SumLeftData + Left(celVal, 3)

End Function

For your example, you would then insert formula in cell C3 (or wherever) that =SumLeftData(B1:B3)


Make sure it's in Module1 - not in Workbook code

How do I add VBA in MS Office?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.