+ Reply to Thread
Results 1 to 9 of 9

SUMIFS Issues

  1. 02-15-2015, 03:05 PM #1
    wat
    wat is offline
    Registered User
    Join Date
    02-15-2015
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    8

    Unhappy SUMIFS Issues

    Hi everyone,

    I have a rather large data set where I am trying to use sumifs to get the data that meets a set of 6 criteria. In the data set I named all the ranges to make the sumifs easier to understand and work with. The data sheet is on one tab and I am working on a new sheet where I clarify the criterion that must be met.

    The problem that I am having is that a number is not getting returned in any of the cells, when I know there is data available, and the one cell that is returning a value is wrong.

    Can someone please help me with this issue, explain the problem, pointing me to another thread, helping with the issue, etc? Anything would be greatly appreciated.

    I have looked around and not found any post about this similar problem, also I have ensured that calculations are automatic. In the past I have used formulas very similar to this and they have worked in other workbooks for me. The file I'm working with is attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-15-2015, 03:16 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: SUMIFS Issues

    HI, welcome to the forum

    Take a look at your "day of week" answers. You have Monday, Mon, Tue, Wed etc, but you are searching for the full day name.

    You can check by applying filters based on your criteria...you will see that on Monday, you only have hour 0, all the other hours are on Mon
    Last edited by FDibbins; 02-15-2015 at 04:31 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 02-15-2015, 03:17 PM #3
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIFS Issues

    Hi.

    First of all, the result in cell E7 is perfectly correct, though I'm not quite sure why your Day of week column contains entries of both "Mon" and "Monday", nor of why you are using the full names to represent days in this column, yet only their equivalent three-letter abbreviations in your results table, which is most likely your issue for the other formulas as well.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  4. 02-15-2015, 03:17 PM #4
    TMS
    TMS is offline
    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
    48,495

    Re: SUMIFS Issues

    Maybe:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Regards, TMS
    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

    Reply With QuoteRegister To Reply
  5. 02-15-2015, 03:21 PM #5
    TMS
    TMS is offline
    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
    48,495

    Re: SUMIFS Issues

    See the attached updated example

    Regards, TMS
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-15-2015, 03:24 PM #6
    wat
    wat is offline
    Registered User
    Join Date
    02-15-2015
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: SUMIFS Issues

    Thank you for the help, much appreciated that was my dumb error.
    Reply With QuoteRegister To Reply
  7. 02-15-2015, 03:25 PM #7
    wat
    wat is offline
    Registered User
    Join Date
    02-15-2015
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: SUMIFS Issues

    I fixed that column in the data sheet and everything worked. Thank you for your response, it was helpful.
    Reply With QuoteRegister To Reply
  8. 02-15-2015, 03:25 PM #8
    TMS
    TMS is offline
    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
    48,495

    Re: SUMIFS Issues

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  9. 02-15-2015, 03:26 PM #9
    wat
    wat is offline
    Registered User
    Join Date
    02-15-2015
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: SUMIFS Issues

    This was a good fix also, and something that I hand't considered using. Thanks for your response.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Does SUMIFS have issues with merged cells or across multiple sheets??
    By dumaser88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-09-2014, 01:04 PM
  2. [SOLVED] Macro "Sumifs" statement syntax issues...
    By herbie226 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2013, 07:24 PM
  3. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  4. [SOLVED] sumifs() having issues with data table over 100k rows
    By sstyre in forum Excel General
    Replies: 10
    Last Post: 12-14-2012, 01:06 PM
  5. SUMIFS formula with date format issues
    By swanseaexcel in forum Excel General
    Replies: 4
    Last Post: 04-18-2011, 05:25 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1