0

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)?

6
  • 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, 2016 at 5:53
  • @dbmitch Yes all values are #.#. And I want to know how to =SUM(...).
    – Jin Kwon
    Jun 29, 2016 at 5:54
  • I assume you can't spilt them into different cells?
    – Dave
    Jun 29, 2016 at 5:56
  • @Dave I wish I can.
    – Jin Kwon
    Jun 29, 2016 at 5:57
  • In that case use left and sum... support.office.com/en-us/article/…
    – Dave
    Jun 29, 2016 at 5:59

2 Answers 2

2

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))

2

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)
    Next

End Function

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

NOTE

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

How do I add VBA in MS Office?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .