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

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

"WRH" wrote:

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

15 kb
105 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)

--
Biff
Microsoft Excel MVP


"WRH" wrote in message
...
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

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

-Bill


"T. Valko" wrote:

15 kb
105 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)

--
Biff
Microsoft Excel MVP


"WRH" wrote in message
...
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

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.

--
Thanks,
Shane Devenshire


"WRH" wrote:

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"WRH" wrote in message
...
Thank you, the array formula did the trick! Now I need to read up on
them!

-Bill


"T. Valko" wrote:

15 kb
105 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)

--
Biff
Microsoft Excel MVP


"WRH" wrote in message
...
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