Sum only numbers in a range with multiple rows ignoring any text?

[ynygma]

Hello all,

I have been researching this problem across the net and have tried everything from arrays to TRIM with no success.

Here is what I am trying to do (using Excel 2010):

This is just a sample, but I have multiple rows and columns A1:G2, in this example both rows are the range I need.

Text	Text	Text	Text	Text 6	Text 4.5	Text/Text
				Text	Text

<tbody>
</tbody>

I have another cell where I will do my calculation. I want to be able to SUM only the numbers in any range I specify ignoring any Text.

I am intermediate when it comes to excel with basic VBA as well but I am open to any assistance I can get

Regards,
Ynygma

 

[jaijaidin]

hello, i have similar kind of problem. cant sum cells with numbers and text.
below is my cells looks like-

A1 4,672.94 EUR

A2 638,817.99 EUR

A3 546,813.02 EUR

A4 11,878.14 EUR i want to add A1 to A3 only numbers, not that 'EUR', how? in real case i have 7000 cell like above and need to sum all.

<tbody>
</tbody>

 

[mfexcel]

An easier approach for your data is to split it into two columns (using Text to Column, Space as delimiter). Then perform SUM as usual,

hello, i have similar kind of problem. cant sum cells with numbers and text.
below is my cells looks like-

A1 4,672.94 EUR

A2 638,817.99 EUR

A3 546,813.02 EUR

A4 11,878.14 EUR i want to add A1 to A3 only numbers, not that 'EUR', how? in real case i have 7000 cell like above and need to sum all.

<tbody>
</tbody>

 

[Rick Rothstein]

hello, i have similar kind of problem. cant sum cells with numbers and text.
below is my cells looks like-

A1 4,672.94 EUR

A2 638,817.99 EUR

A3 546,813.02 EUR

A4 11,878.14 EUR i want to add A1 to A3 only numbers, not that 'EUR', how? in real case i have 7000 cell like above and need to sum all.

<tbody>
</tbody>

Assuming your text is always " EUR", then this formula should work (change the range to suit your actual setup)...

=SUMPRODUCT(SUBSTITUTE(A1:A4," EUR","")+0)