SUM of alphanumeric cells

  • Thread starter Thread starter Why Tea
  • Start date Start date
W

Why Tea

How to use SUM to sum the following cells A1:A6?

10 hours
1 hour
3 hours

123 hours
5 hours

Note, row 4 (A4) is blank.

/Why Tea
 
Can try this array* formula:
=SUM(IF(ISERROR(FIND(" ",A1:A6)),0,LEFT(A1:A6,FIND(" ",A1:A6)-1)*1))
*- array formula must be netered not with 'enter' but with Ctrl+Shift
+Enter. It'll end up with {} around it - those may not be typed.
The formula, howver, asumes that the data structure is:
- number
- space
- something else
The formula would total up all the numbers before the first space it
finds in the text.
 
Back
Top