Sum Non-contiguous Cells & Exclude Text

[tjd59]

I'm using Excel 2010 and am looking for some help to sum non-contiguous cells that have numbers and text in them. For example, I have "17 days" in cell B27, "66 days" in B31, "94 days" in B50 and so on. I would like to sum the numbers and ignore the text. Any help would be appreciated. Thanks!

 

[Caribeiro77]

Maybe:

Book1
	A
1	17 days
2
3
4	66 days
5
6
7
8	94 days
9
10
11
12	177
Sheet1

Cell Formulas
Range	Formula
A12	{=SUM(LEFT(A1,2)+0,LEFT(A4,2)+0,LEFT(A8,2+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[XOR LX]

Hi.

Can you be a bit clearer?

For example, are these cells' contents always of the precise form "# days", where # is some integer?

And re the non-contiguous cells, are some of the cells in between those containing the desired extractions also of this format, so that any solution which attempted a blanket approach over the larger, contiguous range would potentially lead to overcounting?

Regards

 

[tjd59]

Thank you Caribeiro77 this is exactly what I was looking for!

 

[Caribeiro77]

Your welcome..

 

[drangel]

Quick question. How does Excel read (+0) in the formula {=SUM(LEFT(A1,2)+0,LEFT(A4,2)+0,LEFT(A8,2+0))} ? When are these used when writing formulas?

Also, why are two of them outside and the last one inside when using the left formula?

 

[West Man]

Or =LEFT(A1,2)+LEFT(A4,2)+LEFT(A8,2)

 

[Caribeiro77]

drangel, West Man's formula is actually better than mine since you don't have to use CTRL+SHIFT+ENTER( not an array formula)
Due to my formula:

Also, why are two of them outside and the last one inside when using the left formula?

My mistake, you could put the +0 inside or outside the )

The +0 is used when you do a formula that returns text the +0 turns text into a number...

 

[West Man]

drangel, West Man's formula is actually better than mine since you don't have to use CTRL+SHIFT+ENTER( not an array formula)

caribeiro, you formula will also work as a non-array formula

 

[Caribeiro77]

yes you are right West Man
Why don't i try things before post them here??