SUM of alphanumeric cells
 

Thanks AB. It works!Don't really know how it works though :)

One more question, is it possible to have hours in the sum
as well? That is, "142 hours".

On Wednesday, March 30, 2011 7:47:48 PM UTC+11, AB wrote:
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.


On Mar 30, 8:22*am, Why Tea wrote:
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

SUM of alphanumeric cells
 

Slightly adjusted formula (still array formula):
=SUM(IF(ISERROR(FIND(" ",A1:A6)),0,LEFT(A1:A6,FIND(" ",A1:A6)-1)*1))&"
Hours"

On Mar 30, 10:06*am, Why Tea wrote:
Thanks AB. It works!Don't really know how it works though :)

One more question, is it possible to have hours in the sum
as well? That is, "142 hours".



On Wednesday, March 30, 2011 7:47:48 PM UTC+11, AB wrote:
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.

On Mar 30, 8:22*am, Why Tea wrote:
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- Hide quoted text -

- Show quoted text -

SUM of alphanumeric cells
 

Or, you can use the inital formula and use cell formatting instead -
format the cell like:
Format Cell NumberCustom: #" hours"
This way you'll still be able to use the calculated total in some
other formulas if needs be.


On Mar 30, 10:19*am, AB wrote:
Slightly adjusted formula (still array formula):
=SUM(IF(ISERROR(FIND(" ",A1:A6)),0,LEFT(A1:A6,FIND(" ",A1:A6)-1)*1))&"
Hours"

On Mar 30, 10:06*am, Why Tea wrote:



Thanks AB. It works!Don't really know how it works though :)

One more question, is it possible to have hours in the sum
as well? That is, "142 hours".

On Wednesday, March 30, 2011 7:47:48 PM UTC+11, AB wrote:
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.

On Mar 30, 8:22*am, Why Tea wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -