SUM ignoring text fields in a non-string of cells
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: SUM ignoring text fields in a non-string of cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUM ignoring text fields in a non-string of cells

    Hello,

    I am looking to add cell values together, i keep having the value column, an e.g. of what i am trying to add below:

    Cell Person Day 1
    A1 User 1 10
    A10 User 2 0
    A16 User 3 Sick
    A84 User 4 Pto
    A101 User 5 9
    A122 User 6 12

    I am trying to add the amount sold on each day (in the above e.g. Day 1), I want the formula to ignore cells with the word Sick or PTO, i can't use SUM or "<>#N/A" because the cells i am adding up are random and not in a grouped together range, I need to be able to drag the formula across as it will eventually cover a full year.

    Thank you!

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    466
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    Are you trying to find the sum of the Day1 column for all users or for each one?
    Since SUM ignores text, why doesn't =SUM(B:B) work if the DAY column is column B?

    If this is not it, you need to explain what you're looking for.
    Last edited by kweaver; Jul 17th, 2019 at 12:29 PM.

  3. #3
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,819
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    AGGREGATE ignores errors, that might work ?
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    Thank you - I am trying to find the sum of Day 1, however not all the cells in this column (see e.g. see Cell column in my example) - I can't get A:A to or A1:A100 to work as it's not all the cells i need to add up!

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    I;ve tried Aggregate vut still getting the #Value =AGGREGATE(9,6,(D6+D15+D24+D33+D42+D51+D60+D69+D78+D87+D96+D105+D114+D123+D132+D141+D150+D159+D168+D177+D186))

  6. #6
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    466
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    And, why doesn't =SUM(B:B) work?

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    sum(B:B) will total all rows in the column, and not all the values need to be counted in the sum

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    Hi,

    SUM function Ignores non-numeric values in range:

    ABC
    1110
    2this
    32
    4that
    53
    6other
    74

    Sheet690



    Worksheet Formulas
    CellFormula
    C1=SUM(A1:A7)


  9. #9
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    If you may have ERRORs (mixed with Text and Numbers) in range, you can use SUMIF:

    ABC
    1115
    2this
    32
    4#N/A
    53
    6that
    7#DIV/0!
    84
    9#VALUE!
    105

    Sheet690



    Worksheet Formulas
    CellFormula
    C1=SUMIF(A1:A10,">0",A1:A8)


  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM ignoring text fields in a non-string of cells

    Thank you, i've tried that but it's still not working:

    =sumif(D6+D15+D24+D33+D42+D51+D60+D69+D78+D87+D96+D105+D114+D123+D132+D141+D150+D159+D168+D177+D186,">0",D6+D15+D24+D33+D42+D51+D60+D69+D78+D87+D96+D105+D114+D123+D132+D141+D150+D159+D168+D177+D186)


    Think maybe i haven't been super clear, I don't want to sum all the numbers in that column just some of them (see e.g. below)


    Thank you

    Column A Column B Column C
    12 13 sick
    5 sick 11
    pto 54 9
    7 pto 5
    6 6 3
    pto
    10 13
    sick 9 6

    3 5 8
    5 4 4
    pto 6 4
    77 sick 7
    8 9 pto
    9 3 4
    0 sick 4
    4 3 4
    SUM of Red in Column A SUM of Red in Column B SUM of Red in Column C

Some videos you may like

User Tag List

Tags for this Thread

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
  •