Ignore text but not numerics on SUM() function
 

Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K instead
of 1024. Is there a way to use the SUM() function and ignore alpha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.

Ignore text but not numerics on SUM() function
 

A solution that is far from perfect is to remove the K or KB from the text.
So, in a separate column, type the formula (this example supposed that the
1024__ in cell B6, adapt to your needs):
=IF(ISNUMBER(B6), B6, VALUE(SUBSTITUTE(SUBSTITUTE(B6, "KB", ""), "K", "")))

It will not trap other characters though.

Then, you simply make the sum of this column.

Stephane.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1