How to ignore text on SUM() function...

K

KLZA

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.
 
G

Guest

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
 
K

KLZA

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





- Show quoted text -
Click to expand...

Not working. I get #VALUE....
 
T

T. Valko

Worked ok for me, too.

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)
 
T

T. Valko

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")))
 
G

Guest

=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got this
idea from one of your previous posts <g>

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.
 
G

Guest

not sure about the eg data where kb(s) are generated on same format?...

maybe a more versatile formula which will read/release any alpha(s)..can be
interesting?...

--
regards,
driller

*****
- dive with Jonathan Seagull
 
T

T. Valko

Yep, nice one!

I had a feeling it could be reduced even further.

You came up with the *best* solution:
 
T

T. Valko

I think if there are any more conditions/variables then the OP should take
JMB's advice:
 
H

Harlan Grove

T. Valko said:
I had a feeling it could be reduced even further.
Click to expand...
....

and further still
....

=SUMPRODUCT(--(LEFT(A1:A4,SEARCH("K",A1:A4&"K")-1)&".0"))
 
K

KLZA

=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got this
idea from one of your previous posts <g>

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.






- Show quoted text -
Click to expand...

Thanks all!!
 
G

Guest

Probably could be done w/a helper column, but I don't see how to get it all
in one formula w/o ending up w/an array of arrays (for lack of a better
description). But, just because I don't see it doesn't mean it can't be
done.

I would probably resort to a macro before putting the time into finding a
single formula solution (or smack the person who put the alpha data in and
make them redo it).

Sub Test()
Dim lngCount As Long

For lngCount = 65 To 90
Selection.Replace _
what:=Chr(lngCount), _
replacement:="", _
lookat:=xlPart, _
searchorder:=xlByRows, _
matchbyte:=False
Next lngCount
End Sub
 
H

Harlan Grove

JMB said:
Probably could be done w/a helper column, but I don't see how to get it
all in one formula w/o ending up w/an array of arrays (for lack of a
better description). But, just because I don't see it doesn't mean it
can't be done.
Click to expand...

Ugly, but define the name seq referring to

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,64,1))

and use array formulas like

=SUM(--LEFT(colrange,MMULT(--ISNUMBER(-(MID(colrange,1,
TRANSPOSE(seq))&"0")),seq^0)))
I would probably resort to a macro before putting the time into finding
a single formula solution (or smack the person who put the alpha data
in and make them redo it).
Click to expand...
....

Better to download and install Laurent Longre's MOREFUNC.XLL add-in and use

=SUM(--REGEX.MID(A1:A8,"^[-+]?((\d+(\.\d*)?)|((\d*\.)?\d+))"
&"([Ee][-+]?([1-2]?\d{1,2}|30[0-7]))?"))

which should parse any substring Excel would consider numeric with decimal
fractional part and/or scientific notation. Handling fractions, dates and
times would be a bit trickier.
 
T

T. Valko

Harlan Grove said:
Ugly, but define the name seq referring to

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,64,1))

and use array formulas like

=SUM(--LEFT(colrange,MMULT(--ISNUMBER(-(MID(colrange,1,
TRANSPOSE(seq))&"0")),seq^0)))
Click to expand...

seq = 1:64. Why 64? Seems like an arbitrary number smaller than the
"standard" arbitrary 255 because you don't expect a string longer than 64
characters.
 
G

Guest

Ugly, so you say, but still more efficient than anything I can come up with.
Thanks for posting a formula solution - I had tried something w/MMULT, but
couldn't get it to go and was curious to if you would come up w/something.

And, yes I agree the macro approach is quite limited.



Harlan Grove said:
JMB said:
Probably could be done w/a helper column, but I don't see how to get it
all in one formula w/o ending up w/an array of arrays (for lack of a
better description). But, just because I don't see it doesn't mean it
can't be done.
Click to expand...

Ugly, but define the name seq referring to

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,64,1))

and use array formulas like

=SUM(--LEFT(colrange,MMULT(--ISNUMBER(-(MID(colrange,1,
TRANSPOSE(seq))&"0")),seq^0)))
I would probably resort to a macro before putting the time into finding
a single formula solution (or smack the person who put the alpha data
in and make them redo it).
Click to expand...
....

Better to download and install Laurent Longre's MOREFUNC.XLL add-in and use

=SUM(--REGEX.MID(A1:A8,"^[-+]?((\d+(\.\d*)?)|((\d*\.)?\d+))"
&"([Ee][-+]?([1-2]?\d{1,2}|30[0-7]))?"))

which should parse any substring Excel would consider numeric with decimal
fractional part and/or scientific notation. Handling fractions, dates and
times would be a bit trickier.
Click to expand...
 
H

Harlan Grove

T. Valko said:
seq = 1:64. Why 64? Seems like an arbitrary number smaller than the
"standard" arbitrary 255 because you don't expect a string longer
than 64 characters.
Click to expand...

The STRING may be longer than 64 chars, but the numeric substring at
the beginning would have to be no more than 64 chars. Numeric strings
of more than 64 chars aren't going to produce meaningful sums anyway.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ignore text but not numerics on SUM() function 1
Using text and formatting sum function in formula 2
Sum Column above current cell dynamically using function call 7
Using SUM function to count hours BUT ignore Letters? 6
excel sum function 2
Remove/Ignore Letters when Summing cells in a column. 3
formula to make the value in a cell, to a row in a sum function 5
Define variable range input for SUM() function 8

Top