1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • An Excel Question :-)

    From Physfitfreak@21:1/5 to All on Sun Apr 2 16:27:13 2023
    XPost: microsoft.public.excel.misc

    How can one, without the use of VBA, sum over a range of cells some of
    which have number values inside and some text values (depicting a number)?

    Is there a function for that? If not, how do you successfully make your
    sum over that range of cells?

    --
    This email has been checked for viruses by Avast antivirus software. www.avast.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to Physfitfreak on Mon Apr 3 09:45:11 2023
    XPost: microsoft.public.excel.misc

    Physfitfreak wrote on 02/04/2023 :
    sum over a range of cells some of which have number values inside and some text values (depicting a number)?

    excel 2021 does it with the standard SUM()

    10
    '15
    26
    '38

    the SUM below these cells gives 36

    --
    /-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
    -=- -=- -=- -=- -=- -=- -=- -=- - -=-
    ........... [ al lavoro ] ...........

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Physfitfreak@21:1/5 to Ammammata on Mon Apr 3 17:02:52 2023
    On 4/3/2023 2:45 AM, Ammammata wrote:
    Physfitfreak wrote on 02/04/2023 :
    sum over a range of cells some of which have number values inside and
    some text values (depicting a number)?

    excel 2021 does it with the standard SUM()

    10
    '15
    26
    '38

    the SUM below these cells gives 36



    I want to find a way to use a formula to sum over both numbers and
    texts. So if some of the cells have a texts that say the number, I want
    them all be included in the sum.

    So in your list, I am looking for a formula that gives the sum of 10,
    15, 26, and 38. Not just the sum of 10 and 26.

    Can you write such a formula?

    --
    This email has been checked for viruses by Avast antivirus software. www.avast.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Physfitfreak@21:1/5 to Ammammata on Mon Apr 3 17:01:37 2023
    On 4/3/2023 2:45 AM, Ammammata wrote:
    Physfitfreak wrote on 02/04/2023 :
    sum over a range of cells some of which have number values inside and
    some text values (depicting a number)?

    excel 2021 does it with the standard SUM()

    10
    '15
    26
    '38

    the SUM below these cells gives 36


    I want to find a way to use a formula to sum over both numbers and
    texts. So if some of the cells have a texts that say the number, I want
    them all be included in the sum.

    So in your list, I am looking for a formula that gives the sum of 10,
    15, 26, and 38. Not just the sum of 10 and 26.

    Can you write such a formula?

    --
    This email has been checked for viruses by Avast antivirus software. www.avast.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Physfitfreak@21:1/5 to Ammammata on Mon Apr 3 17:04:26 2023
    On 4/3/2023 2:45 AM, Ammammata wrote:
    Physfitfreak wrote on 02/04/2023 :
    sum over a range of cells some of which have number values inside and
    some text values (depicting a number)?

    excel 2021 does it with the standard SUM()

    10
    '15
    26
    '38

    the SUM below these cells gives 36



    I want to find a way to use a formula to sum over both numbers and
    texts. So if some of the cells have a texts that say the number, I want
    them all be included in the sum.

    So in your list, I am looking for a formula that gives the sum of 10,
    15, 26, and 38. Not just the sum of 10 and 26.

    Can you write such a formula?

    --
    This email has been checked for viruses by Avast antivirus software. www.avast.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Alex Plantema@21:1/5 to All on Mon Apr 10 13:50:32 2023
    XPost: microsoft.public.excel.misc

    Op zo 02-04-2023 om 23:27 schreef Physfitfreak:
    How can one, without the use of VBA, sum over a range of cells some of which have number values inside and some text values (depicting a number)?

    Is there a function for that? If not, how do you successfully make your sum over that range of cells?


    That's not possible. Summing numbers stored as text using plus signs gives different results than using the SUM function.
    Both methods should give an error message.
    Format the cells as numbers and use Text-to-columns to convert the contents to true numbers.

    --
    Alex.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)