Results 1 to 3 of 3

Thread: SUMIFS with date periods and names as criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIFS with date periods and names as criteria

    Hi everyone, I am trying to use the SUMIFS formula to capture sums with 1) date criteria (i.e. all hours between Date 1 and Date 2) and specific names (i.e. upwards of 40 distinct names).

    I have attached a worksheet sample below. For example, I would like to know the total number of hours for Adam Smith for the month of September. Note that the month period varies based on accounting month so I cannot use a typical September start and end date. See Columns M and N ==> I included the specific date periods that would be helpful.

    I entered the formula used (see Adam Smith formula). I receive a #VALUE! error message. I tried the formula with a smaller set of names and manually entered the name (i.e. "Adam Smith") but with 40+ names and people coming in and going out, manually entering names cannot be best practice. Any help with this would greatly be appreciated.

    Thanks everyone!!!!!



    Columns A B C D E F G H I J K L M N
    1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 2-Nov 9-Nov 10-Nov Start End
    Adam Smith 1 3 6 7 21 9 22 55 32 September 1-Sep 25-Sep
    Brian Davis 1 2 4 2 10 33 3 19 October 26-Sep 30-Oct
    George Washington 44 2 44 38 29 11 12 44 20 November 31-Oct 27-Nov
    Target Answer
    September Total November Total September Total October Total November Total
    Adam Smith =SUMIFS(B3:J5, A3:A5,A8,B2:J2,">="&M2,B2:J2,"<="&N2) 47 0 109
    Brian Davis 19 0 55
    George Washington 168 0 76


  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,264
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: SUMIFS with date periods and names as criteria

    Maybe this


    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    2
    01/set
    02/set
    03/set
    04/set
    05/set
    06/set
    02/nov
    09/nov
    10/nov
    Start
    End
    3
    Adam Smith
    1
    3
    6
    7
    21
    9
    22
    55
    32
    September
    01/set
    25/set
    4
    Brian Davis
    1
    2
    4
    2
    10
    33
    3
    19
    October
    26/set
    30/out
    5
    George Washington
    44
    2
    44
    38
    29
    11
    12
    44
    20
    November
    31/out
    27/nov
    6
    7
    September
    October
    November
    8
    Adam Smith
    47
    0
    109
    9
    Brian Davis
    19
    0
    55
    10
    George Washington
    168
    0
    76
    11


    Formula in B8
    =SUMPRODUCT(($B$3:$J$5)*($A$3:$A$5=$A8)*($B$2:$J$2>=INDEX($M$3:$M$5,MATCH(B$7,$L$3:$L$5,0)))*($B$2:$J$2<=INDEX($N$3:$N$5,MATCH(B$7,$L$3:$L$5,0))))
    copy across till D8 and down

    Hope this helps

    M.

  3. #3
    New Member
    Join Date
    Sep 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIFS with date periods and names as criteria

    Hey bud!

    I have also solved this with a combo of index/match and sumifs. In the top-left cell I have =SUMIFS(INDEX($B$2:$J$4,MATCH($A7,$A$2:$A$4,0),),$B$1:$J$1,">="&M$7,$B$1:$J$1,"<="&M$8) (correct cell references as appropriate). I have transposed the look-up table to make it easier to copy the formula across.

    Essentially, what is going on here are that there are two lookups. Firstly, the index match finds the row that matches the employee name. Then, the sumifs uses that row to find the cells matching the date constraints.

    Cheers!
    Marcus


    1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep 2-Nov 9-Nov 10-Nov Start End
    Adam Smith 1 3 6 7 21 9 22 55 32 September 1-Sep 25-Sep
    Brian Davis 1 2 4 2 10 33 3 19 October 26-Sep 30-Oct
    George Washington 44 2 44 38 29 11 12 44 20 November 31-Oct 27-Nov
    September October November September Total October Total November Total September October November
    Adam Smith 47 0 109 47 0 109 Start 1-Sep 26-Sep 31-Oct
    Brian Davis 19 0 55 19 0 55 End 25-Sep 30-Oct 27-Nov
    George Washington 168 0 76 168 0 76

Some videos you may like

User Tag List

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
  •