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
)?
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
)?
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
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 – 2016-06-29T05:53:38.007
@dbmitch Yes all values are
#.#
. And I want to know how to=SUM(...)
. – Jin Kwon – 2016-06-29T05:54:58.437I assume you can't spilt them into different cells? – Dave – 2016-06-29T05:56:19.680
@Dave I wish I can. – Jin Kwon – 2016-06-29T05:57:47.203
In that case use left and sum... https://support.office.com/en-us/article/LEFT-LEFTB-functions-9203d2d2-7960-479b-84c6-1ea52b99640c
– Dave – 2016-06-29T05:59:27.977@Dave You're right. And how can I sum of all cells which each is
LEFT
ed? – Jin Kwon – 2016-06-29T06:03:03.813