How do I sum cells and ignore text

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How do I sum cells and ignore text

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    309
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do I sum cells and ignore text

     
    I have cells A1 through A10 which can contain numbers or text. I want to be able to sum this range while always ignoring the ones with text so as to eliminate the error when summing.

    Any ideas?

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    =SUM(A1:A10)

    Excel will ignore the non-numeric entries.
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Posts
    309
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I get value error when their is a non numeric in one of the cells?

  4. #4
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Can you post the values that you have in A1:A10 please?

  5. #5
    Board Regular
    Join Date
    Jul 2002
    Posts
    309
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry,

    I see why. It's because I'm attempting to subrtract and/or multiply cells when some have text in them, then I add this column up.
    So, the question now is if one of 2 cells that I am subracting and/or multiplying by a factor contains text, how do I get around it?

  6. #6
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Use a formula like

    =IF(AND(ISNUMBER(F1),ISNUMBER(G1)),F1*G1,"")

    which will leave the result cell blank if either of the values that you are trying to multiply is non-numeric.
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,812
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you post the formula that gives you a #VALUE error....and indicate which cells might contain text?

    In general you could probably use a formula like

    =IF(ISERR(Your_formula),"",Your_formula)

    ....although you might be able to simplify that if only certain cells can contain text.

    Alternatively you can sum C1:C10, while ignoring errors with

    =SUM(SUMIF(C1:C10,{">0","<0"}))

  8. #8
    Board Regular
    Join Date
    Jul 2002
    Posts
    309
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The 2 cells I am calculating are formatted as a date, should I be using some other phrase instead of isnumber?

  9. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,812
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dates are simply formatted numbers in Excel so ISNUMBER should work just fine....although you might like to describe exactly what you are tring to achieve and what formula you are currently using

  10. #10
    Board Regular
    Join Date
    Jan 2007
    Posts
    1,152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I don't know if this is relevant to your problem, but I frequently find text substututed for numbers and the easiest way to avoid the problem is to use the SUM function as it ignores text.

    Instead of =A1*B1, use =sum(A1)*sum(B1). This will give you a zero in the event that either cell contains text.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com