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 aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.

try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter

"KLZA" wrote:

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 aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1*:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
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 aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
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 aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
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 aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

Here's another one that's a few keystrokes shorter and is normally entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
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 aplha
characters but not numerics? This way if someone types 1024K or
1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....