+ Reply to Thread
Results 1 to 9 of 9

SUMIF not working consistenly

  1. #1
    Registered User
    Join Date
    12-29-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    SUMIF not working consistenly

    Hi All, I am new to this forum and hoping someone can help me as I am having trouble with SUMIF.

    The issue I am having is that in the same column I am using the same formula, just changing the criteria to what I need. Though in certain instances it works as it should and in other instances, it returns 0 when I know it should return a result. Its a simple formula of =SUMIF($C$4:$L$100,"Beach",$G$4:$G$100) that returns a 0 but =SUMIF($C$4:$L$100,"Hoody Anorak",$G$4:$G$100) returns a result as it is meant to.

    I have highlighted the instances in red in column Q as I am having the same issue with the Days.

    I have attached a copy of the spreadsheet and would love to know what I am doing wrong???
    (I have changed some of the data to be text or Date but doesn't seem to matter)
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-28-2020
    Location
    ADELAIDE, SOUTH AUSTRALIA
    MS-Off Ver
    VERSION 16.44 (MAC)
    Posts
    5

    Re: SUMIF not working consistenly

    You have a space after Beach and after Beanie.

    Remove the space or add it to your formulae and it works.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: SUMIF not working consistenly

    Well, it's usual to just match against one column, in this case, column C. That aside, it could be something as simple as a trailing space on the entries you are matching against.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    12-29-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    Re: SUMIF not working consistenly

    THANK YOU!!!!! That was driving me insane and such a simple solution.

    Could you look at =SUMIF($B$4:$L$100,"MON",$G$4:$G$100) and see if you can see why it doing the same???

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: SUMIF not working consistenly

    You're welcome. Thanks for the rep.

    I'd guess this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    should be this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    assuming that column H has text in it and is not a date formatted as "dd"

    I'm viewing this on an iPad so I can only go on what it looks like, not what might actually be there.

  6. #6
    Registered User
    Join Date
    12-29-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    Re: SUMIF not working consistenly

    NO worries and cheers for that, it worked.

    I was being a bit lazy and thought I could group it all together and when I needed to get more information (ie total sales) I could copy the formula and just change the sum range. Bit of a noob but appreciate the help and quick responses

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: SUMIF not working consistenly

    Again, you're welcome. I think you got away with the first formula because it was actually using the first column in the range. Second one, not so lucky.

  8. #8
    Registered User
    Join Date
    12-29-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    Re: SUMIF not working consistenly

    Make sense and helps to know this.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: SUMIF not working consistenly

    Ok. Better try and get some sleep now 🤫😴👍

+ 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. [SOLVED] sumif not working.....
    By Phineflower in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-11-2016, 01:26 PM
  2. SumIF not working
    By lucky3 in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 03-18-2012, 04:30 AM
  3. vba working like a sumif
    By jartzh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-08-2011, 10:54 AM
  4. Worksheet Events Not Working Consistenly
    By incjourn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2009, 05:57 AM
  5. Worksheet Events Not Working Consistenly
    By incjourn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2009, 07:34 AM
  6. [SOLVED] SumIF is not working
    By DanVDM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] SumIF is not working
    By DanVDM in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2005, 12:05 PM

Tags for this Thread

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