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`

)?

Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.

Sign up to join this community
Anybody can ask a question

Anybody can answer

The best answers are voted up and rise to the top

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`

)?

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

`#.#`

. And I want to know how to`=SUM(...)`

. – Jin Kwon Jun 29 '16 at 5:541more comments