Re: Text and numbers

[paul]

Using Excel 2003 I want to get the sum of cells containing numbers, but the
these cells also contain some text. I only want the sum of the numbers.

Please help. P

[David McRitchie]

Hi Paul,

=SUM(A1:A20)
or any other range will ignore cells with text values, and ignore empty cells
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"paul" <[email protected]> wrote in message news:[email protected]...
> Using Excel 2003 I want to get the sum of cells containing numbers, but the
> these cells also contain some text. I only want the sum of the numbers.
>
> Please help. P
>
>

[Ragdyer]

Are text and numbers in the *same* cell?

If they are, post explicit examples.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"paul" <[email protected]> wrote in message
news:[email protected]...
> Using Excel 2003 I want to get the sum of cells containing numbers, but
the
> these cells also contain some text. I only want the sum of the numbers.
>
> Please help. P
>
>

[paul]

Okay, here goes. I use W5 in one cell, the next cell in that row might be
R5. These are the only letters I use, but the numbers will change, but
never more than 9.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Are text and numbers in the *same* cell?
>
> If they are, post explicit examples.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "paul" <[email protected]> wrote in message
> news:[email protected]...
>> Using Excel 2003 I want to get the sum of cells containing numbers, but
> the
>> these cells also contain some text. I only want the sum of the numbers.
>>
>> Please help. P
>>
>>
>

[RagDyeR]

If your data is in A1 to E1,
AND
You only have one letter and one number in a cell, try this *array* formula:

=SUM(--RIGHT(A1:E1))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.


If, on the other hand, you have *more* then one number combined with the
single letter, try this *array* formula:

=SUM(--RIGHT(A1:E1,LEN(A1:E1)-1))

Don't forget the CSE.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



"paul" <[email protected]> wrote in message
news:[email protected]...
Okay, here goes. I use W5 in one cell, the next cell in that row might be
R5. These are the only letters I use, but the numbers will change, but
never more than 9.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------
"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Are text and numbers in the *same* cell?
>
> If they are, post explicit examples.
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "paul" <[email protected]> wrote in message
> news:[email protected]...
>> Using Excel 2003 I want to get the sum of cells containing numbers, but
> the
>> these cells also contain some text. I only want the sum of the numbers.
>>
>> Please help. P
>>
>>
>

[paul]

Thank you very much. It works fine now. P


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> If your data is in A1 to E1,
> AND
> You only have one letter and one number in a cell, try this *array*
> formula:
>
> =SUM(--RIGHT(A1:E1))
>
> --
> Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of
> the
> regular <Enter>, which will *automatically* enclose the formula in curly
> brackets, which *cannot* be done manually. Also, CSE *must* be used when
> revising the formula.
>
>
> If, on the other hand, you have *more* then one number combined with the
> single letter, try this *array* formula:
>
> =SUM(--RIGHT(A1:E1,LEN(A1:E1)-1))
>
> Don't forget the CSE.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
>
>
> "paul" <[email protected]> wrote in message
> news:[email protected]...
> Okay, here goes. I use W5 in one cell, the next cell in that row might be
> R5. These are the only letters I use, but the numbers will change, but
> never more than 9.
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> -----------
> "Ragdyer" <[email protected]> wrote in message
> news:[email protected]...
>> Are text and numbers in the *same* cell?
>>
>> If they are, post explicit examples.
>> --
>> Regards,
>>
>> RD
>>
>> --------------------------------------------------------------------------
> -
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> --------------------------------------------------------------------------
> -
>> "paul" <[email protected]> wrote in message
>> news:[email protected]...
>>> Using Excel 2003 I want to get the sum of cells containing numbers, but
>> the
>>> these cells also contain some text. I only want the sum of the numbers.
>>>
>>> Please help. P
>>>
>>>
>>
>
>
>

[Ragdyer]

You're welcome!
Thanks for the feed-back.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"paul" <[email protected]> wrote in message
news:[email protected]...
> Thank you very much. It works fine now. P
>
>
> "RagDyeR" <[email protected]> wrote in message
> news:[email protected]...
> > If your data is in A1 to E1,
> > AND
> > You only have one letter and one number in a cell, try this *array*
> > formula:
> >
> > =SUM(--RIGHT(A1:E1))
> >
> > --
> > Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of
> > the
> > regular <Enter>, which will *automatically* enclose the formula in curly
> > brackets, which *cannot* be done manually. Also, CSE *must* be used
when
> > revising the formula.
> >
> >
> > If, on the other hand, you have *more* then one number combined with the
> > single letter, try this *array* formula:
> >
> > =SUM(--RIGHT(A1:E1,LEN(A1:E1)-1))
> >
> > Don't forget the CSE.
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> >
> >
> > "paul" <[email protected]> wrote in message
> > news:[email protected]...
> > Okay, here goes. I use W5 in one cell, the next cell in that row might
be
> > R5. These are the only letters I use, but the numbers will change, but
> > never more than 9.
>
> --------------------------------------------------------------------------
--
>
> --------------------------------------------------------------------------
--
> > -----------
> > "Ragdyer" <[email protected]> wrote in message
> > news:[email protected]...
> >> Are text and numbers in the *same* cell?
> >>
> >> If they are, post explicit examples.
> >> --
> >> Regards,
> >>
> >> RD
> >>
>
>> -------------------------------------------------------------------------
-
> > -
> >> Please keep all correspondence within the NewsGroup, so all may benefit
!
>
>> -------------------------------------------------------------------------
-
> > -
> >> "paul" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> Using Excel 2003 I want to get the sum of cells containing numbers,
but
> >> the
> >>> these cells also contain some text. I only want the sum of the
numbers.
> >>>
> >>> Please help. P
> >>>
> >>>
> >>
> >
> >
> >
>
>