+ Reply to Thread
Results 1 to 9 of 9

Sumif function not working correctly

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    eugene, or
    MS-Off Ver
    Excel 2011
    Posts
    74

    Sumif function not working correctly

    Hi everyone, I got some help making a sumif function work earlier today and now it seems like it is, however, the 5 sums that are generated are off.

    The first one grabs all the sums of column K for every ash tree in column G

    Please Login or Register  to view this content.
    The second counts sums of column K for every ash tree in column C where the word "poor" appears in column M

    Please Login or Register  to view this content.
    The third counts sums of column K for every ash tree in column C where the word "fair" appears in column M

    Please Login or Register  to view this content.
    The counts sums of column K for every ash tree in column C where the word "good" appears in column M

    Please Login or Register  to view this content.
    The fofth counts sums of column K for every ash tree in column C where the no word appears in column M

    Please Login or Register  to view this content.
    All of that seems to work however the first sum pulls out 2186

    The second pulls out 62

    The thirds pulls out 82

    The fourth pulls out 1831

    The fifth pulls out 238

    The way understand it the second through 4th sums should equal the first sum of 2186, but they add to 2213

    Can anyone see where I've gone wrong? I've attached a sample file. Thanks!
    Attached Files Attached Files

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

    Re: Sumif function not working correctly

    There are some cells in M6:M93 that contain BOTH words Fair and Poor.
    So they will be counted in both the formulas for Fair and Poor.

  3. #3
    Registered User
    Join Date
    02-11-2013
    Location
    eugene, or
    MS-Off Ver
    Excel 2011
    Posts
    74

    Re: Sumif function not working correctly

    Duh! Thank you. Is there a good way to search the word "poor" as long as the word "fair" is also not in the same box and vise versa?

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

    Re: Sumif function not working correctly

    I would recommend making a 4th category...

    Poor - Fair - Good - Great
    Or whatever..

  5. #5
    Registered User
    Join Date
    02-11-2013
    Location
    eugene, or
    MS-Off Ver
    Excel 2011
    Posts
    74

    Re: Sumif function not working correctly

    Thank you again for your help. I've been looking around at try to add on last bit of functionality to the form, instead of adding the numbers in column K to get the total DBH, for example in every instance an Ash is found in poor condition it is added to a running tally to get the total number of ash trees in poor condition

  6. #6
    Registered User
    Join Date
    02-11-2013
    Location
    eugene, or
    MS-Off Ver
    Excel 2011
    Posts
    74

    Re: Sumif function not working correctly

    It seems like COUNTIFS is the best way to go, but I'm not sure how to make it work

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

    Re: Sumif function not working correctly

    Pretty much the same as SUMFIS, just change SUMIFS to COUNTIFS
    And remove the range that is summed..

    =SUM(SUMIFS(K6:K935,G6:G935,{"Green Ash","White Ash","Ash"}))
    change to
    =SUM(COUNTFS(G6:G935,{"Green Ash","White Ash","Ash"}))

  8. #8
    Registered User
    Join Date
    02-11-2013
    Location
    eugene, or
    MS-Off Ver
    Excel 2011
    Posts
    74

    Re: Sumif function not working correctly

    Thanks! This seems to work for checking both criteria!

    Please Login or Register  to view this content.

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

    Re: Sumif function not working correctly

    Actually, for that you don't need the SUM part.
    That's only needed when you're doing an {array of criteria}...to do an OR

    So just
    COUNTIFS(G6:G935,"*Ash*",M6:M935,"*Good*")
    would suffice.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF function not returning expected result
    By kapeller in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-21-2014, 10:12 AM
  2. [SOLVED] Using the Split function with another function incorporated is not working correctly
    By highlystrung in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-06-2013, 03:51 PM
  3. If function not working correctly
    By lsbeuk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-22-2011, 09:26 PM
  4. IF Function Not Working Correctly(Regarding the > symbol)
    By MSchleicher in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-17-2011, 04:52 PM
  5. [SOLVED] Lookup function still not working correctly
    By Mike K in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-31-2005, 10:05 PM

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