• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum of cells containing text and numbers combined

E

EldoFuoj

New Member
If i wanted to sum the cells of a row that contain 3 4 5 1-abc 4-abc, how would I make excel sum the total of all the numbers and the numbers before the hyphen in each cell. I am currently just getting the total of 12, but it should be 17. Easy one I know, but I can't seem to find the answer using the search.
 
Eldofuoj


If you only have 1 number before the - use:

Code: 
=SUMPRODUCT(1*LEFT(A1:E1,1))

or

[code]=SUM(1*LEFT(A1:E1,1)) [b]Ctrl Shift Enter


If you have nultiple numbers in front of the - like 45-abc, use:

=SUMPRODUCT(1*LEFT(A1:E1,IFERROR(FIND("-",A1:E1)-1,1)))

or

=SUM(1*LEFT(A1:E1,IFERROR(FIND("-",A1:E1)-1,1)))[/code]Ctrl Shift Enter[/b]
 
Wow, that was so easy. Thanks you so much. I understand the 1*left function instructs excel to look to the left of the cell, but what is the ,1 at the end of the range signify? Also, if i had a cell that just contained ABC, how do i keep the formula from returning an error now?
 
EldoFuoj


The LEFT(A1:E1,1) part extracts the left character from each cell

The 1* converts it from a Text value to a number
 
and as for the question:

Code: 
=SUMPRODUCT(1*IFERROR(1*LEFT(A4:E4,IFERROR(FIND("-",A4:E4)-1,1)),0))
Ctrl Shift Enter
 
You must log in or register to reply here.
Back
Top