# Sumif w/Text conversion on a range of cells

W

#### WRH

I'm using Excel 2007, and would like to convert a range of cells from text to
a value based upon the word 'data'. I want to sum up the kb of data being
used. To do this, I cannot use value unless I strip off the kb from the
cell. I can do that with 1 cell, but how can I do this with a range?

Thanks for any help!

-Bill

call 10 calls 22 =SUMIF(A3:A8,"call",B3:B8)
call 12 text 2 =COUNTIF(A3:A8,"text")
text 1 msg data - 1 cell 15 =LEFT(B7,LEN(B7)-2)
text 1 msg Data - Range ???
data 15 kb
data 105 kb

M

#### Mike H

Hi,

I'd create a 'helper' column.
Put his in c1 and drag down
=IF(ISERROR(FIND(" ",B1)),B1,LEFT(B1,FIND(" ",B1))*1)
You can then hide the helper column or column B

then this formula will do the sums
=SUMIF(\$A\$1:\$A\$6,"Call",\$C\$1:\$C\$6)
=SUMIF(\$A\$1:\$A\$6,"Text",\$C\$1:\$C\$6)
=SUMIF(\$A\$1:\$A\$6,"Data",\$C\$1:\$C\$6)

Mike

T

#### T. Valko

15 kb

Assuming there is *always* a space between the number value and "kb" and
there is *always* a number value when "kb" is present in the cell.

Try this array formula** :

=SUM(IF((A1:A5="data")*(RIGHT(B1:B5,2)="kb"),--LEFT(B1:B5,LEN(B1:B5)-3)))

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

W

#### WRH

Thank you, the array formula did the trick! Now I need to read up on them!

-Bill

S

#### ShaneDevenshire

Hi,

here's a formula that does the job:

=SUM((A1:A6="data")*SUBSTITUTE(IF(B1:B6="",0,B1:B6),"kb",))

This formula needs to be Array Entered (Shift+Ctrl+Enter). It doesn't care
about the space before Kb, and its short.

If this helps, please click the Yes button.

T

#### T. Valko

You're welcome. Thanks for the feedback!