+ Reply to Thread
Results 1 to 17 of 17

Sum of numeric values within cells also containing non-numeric characters

  1. #1
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Sum of numeric values within cells also containing non-numeric characters

    Hello,
    I need to calculate the sum of cells in a column as below, eg

    12
    20
    18*
    4*
    0
    65*
    10

    Where the answer would be 129

    In other words the total ignores the asterisks (and yes these are asterisks not wild cards)

    Any assistance greatly appreciated, thanks.

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Sum of numeric values within cells also containing non-numeric characters

    try:

    Please Login or Register  to view this content.
    if you have blank cells in the range:

    Please Login or Register  to view this content.
    Last edited by icestationzbra; 04-22-2013 at 12:57 PM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  3. #3
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: Sum of numeric values within cells also containing non-numeric characters

    thanks icestationzbra, unfortunately that returns the dreaded #value! when I try it?

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Sum of numeric values within cells also containing non-numeric characters

    It works for me.

    Make sure there are no blanks or other NON numeric values (like Hello*) in the range.

  5. #5
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: Sum of numeric values within cells also containing non-numeric characters

    tyvm to both of you, it works on one computer and not the other so i'll cut and paste the two sheets and establish what the glitch is but my main dilemma is solved, thank you

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,462

    Re: Sum of numeric values within cells also containing non-numeric characters

    Anyway, try to replace blank cells with 0
    =SUMPRODUCT(SUBSTITUTE(IF(A2:A8="",0,A2:A8),"*","")*1)
    Conffirmed with Ctrl-Shift-Enter

  7. #7
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: Sum of numeric values within cells also containing non-numeric characters

    ok i see the difference now, with blank cells in the range it doesnt work. I need to have blanks in the range and these cannot be zero but must be blank as they will populate over time. Any suggestions for this extended issue gatefully received

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,462

    Re: Sum of numeric values within cells also containing non-numeric characters

    To avoid blank cells, have you tried my solution in #6?

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Sum of numeric values within cells also containing non-numeric characters

    Try this array formula entered with CTRL + SHIFT + ENTER

    =SUM(IF(A1:A10<>"",SUBSTITUTE(A1:A10,"*","")+0))

  10. #10
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: Sum of numeric values within cells also containing non-numeric characters

    @Jonmo1 ...... now that my friend, is pure genius, many thanks.

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Sum of numeric values within cells also containing non-numeric characters

    Or, confirmed with just Enter,

    =SUMPRODUCT(--(0&SUBSTITUTE(A1:A10,"*","")))
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Sum of numeric values within cells also containing non-numeric characters

    That's great!
    Quote Originally Posted by shg View Post
    =SUMPRODUCT(--(0&SUBSTITUTE(A1:A10,"*","")))

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Sum of numeric values within cells also containing non-numeric characters

    Almost great -- it doesn't work with negative numbers ...

  14. #14
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,462

    Re: Sum of numeric values within cells also containing non-numeric characters

    shg: almost almost great, except cells with text
    jonmo: work great all the cases.

  15. #15
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Sum of numeric values within cells also containing non-numeric characters

    Quote Originally Posted by bebo021999 View Post
    jonmo: work great all the cases.
    Mine doesn't work on cells with text either..
    Need to nest some if's to test for text entries..

  16. #16
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Sum of numeric values within cells also containing non-numeric characters

    {=sumproduct(iferror(--substitute(a1:a10,"*",""),0))}

  17. #17
    Registered User
    Join Date
    04-22-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: Sum of numeric values within cells also containing non-numeric characters

    hello to all on this thread.... just anote to say thanks to all for the way in which you extended the debate on this problem and then solved it......since my original post I have extended my database and have scenarios where the range contains, not only blanks but also text., so your intellect has not been wasted, thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1