Results 1 to 5 of 5

Thread: Sumifs lower than a given day

  1. #1
    Board Regular
    Join Date
    Nov 2007
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sumifs lower than a given day

    Hello
    I'm trying to do a sumifs formula based on 2 criterias. The first is a name.

    the second one I can't do is when the date DAY is lower than 15.

    For example in the table below I want to get the sum of A only lower than 15, the result should be 2.000

    I was trying something (hardcoded just for clarity) like: +sumifs(value, name, "A", day(date),"<15")
    But it doesn't go through, also thought to put them between {} but it doesn't work.
    I don't want to do a 4th column with the days, but if its not possible I guess I can do that.

    Name Date Value
    A 09/11/2019 1000
    A 09/13/2019 1000
    A 09/16/2019 1000
    B 09/14/2019 1000
    B 09/16/2019 1000

    Thanks for the help

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,487
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Sumifs lower than a given day

    Next Download

    CDEF
    1NameDateValue
    2A9/11/201910002000
    3A9/13/20191000
    4A9/16/20191000
    5B9/14/20191000
    6B9/16/20191000

    Spreadsheet Formulas
    CellFormula
    F2=SUMPRODUCT((C2:C6="A")*(DAY(D2:D6)<15),E2:E6)


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Nov 2007
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumifs lower than a given day

    Perfect!
    Thanks a lot!

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Sumifs lower than a given day

    Try

     ABCD
    1NameDateValue 
    2A11/09/201910002000
    3A13/09/20191000 
    4A16/09/20191000 
    5B14/09/20191000 
    6B16/09/20191000 

    CellArray Formula
    D2{=SUM(IF(A2:A6="A",IF(DAY(B2:B6)<15,C2:C6)))}


    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Regards Dante Amor

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,487
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Sumifs lower than a given day

    Quote Originally Posted by el_ja View Post
    Perfect!
    Thanks a lot!
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

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
  •