+ Reply to Thread
Results 1 to 6 of 6

SumIfs problem

  1. #1
    Registered User
    Join Date
    10-12-2011
    Location
    FL
    MS-Off Ver
    Excel 2010
    Posts
    11

    SumIfs problem

    ok, here is the situation in Sheet 1 Column B I have the date that a person arrived. In Sheet 1 Column C I have the time the person arrived. In Sheet 3 Column A I have the the date, in Sheet 2 Column C I have the time the shift started and in Sheet 3 Column C I have the time the shift ended. In Sheet 3 Column D I have a number which indicates how many people were on that shift, some shift times overlap.

    What I need to do is count how many people were working when the customer arrived and place that number in a column on sheet 1.

    This is what I came up with but does not work properly.

    =SUMIFS(Sheet3!$D$1:$D$605,Sheet3!$A$1:$A$605,"="&Sheet1!B2,Sheet3!$B$1:$B$605,"<="&Sheet1!C2,Sheet3!$C$2:$C$606,">="&Sheet1!C2)

    Can someone give me some guidance?

  2. #2
    Registered User
    Join Date
    07-17-2012
    Location
    Manama,Bahrain
    MS-Off Ver
    2003, 2007
    Posts
    71

    Re: SumIfs problem

    Hi Krombie,

    Need to look at the example.


    However you have put the formula as SUMIFS and not SUMIF, unless its a new formula that has been introduced in 2010

    Most probably by now excel would point to you "~ too many arguments~" as SumIf only takes 3 arguments in a normal scenario.


    Regards

    Chuck

  3. #3
    Registered User
    Join Date
    10-12-2011
    Location
    FL
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: SumIfs problem

    Sumifs is a new formula in Excel 2010

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,735

    Re: SumIfs problem

    sumifs() was introduced in 2007, and im a little rusty, but i have a feeling it wont work across sheets.

    as a test, copy/reference a portion of your data from your other sheet/s to sheet3 and then try it again based onthose values?
    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

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: SumIfs problem

    your column C range is offset by one row from column B range...is that correct?

    What is the problem? Is the formula not being allowed? Or are you getting incorrect results?

    I entered the formula and it indicated a problem and suggested the below fix:

    =SUMIFS(Sheet3!$D$1:$D$605,Sheet3!$A$1:$A$605,"="&Sheet1!B2,Sheet3!$B$1:$B$605,"<="&Sheet1!C2,Sheet3 !$C$2:$C$606,">="&Sheet1!C2)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  6. #6
    Registered User
    Join Date
    10-12-2011
    Location
    FL
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: SumIfs problem

    I was getting incorrect figures. I got it figured out though, my issue was that for the shift ending times I was not using 3:59:59 PM instead of 4:00:00 PM. But my formula was correct.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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