Results 1 to 7 of 7

Thread: Sumproduct: Dates by year matched against certain terms, ignore blanks/text

  1. #1
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013

    Sumproduct: Dates by year matched against certain terms, ignore blanks/text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,

    I'm building a tracker for my office and must delineate certain terms in Column A with dates in Column H from this year.

    My attempt:
    =SUMPRODUCT((A10:A10000="Active")*(H10:H10000=year(today()))

    Also:
    =COUNTIFS(A10:A10000,"Active",H10:H10000,YEAR(TODAY()))
    =INDEX( MATCH("Active",$A$10:$A$11,0), MATCH(YEAR(TODAY()),$H$10:$H$11,0))
    =SUMPRODUCT(($A$10:$A$11="Active")*(isdate($H$10:$H$11=YEAR(TODAY()))))
    =SUMPRODUCT((A10:A10000="Active")*--(H10:H10000<>"")*--(YEAR(H10:H10000)=2017))

    I must also avoid text and blanks input in the date column.

    Help!
    Christian
    Information Designer
    National Capital Region

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Why does the first Sumproduct() not work? It will avoid counting blanks and text entries


  3. #3
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013
    I don't know. I just get "0," despite verifying that there are, indeed, dates within the range.
    Christian
    Information Designer
    National Capital Region

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Are you able to post a sample workbook. So we can. Checked what's going on?


  5. #5
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013
    See attached.
    Attached Files Attached Files
    Christian
    Information Designer
    National Capital Region

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try this:

    =SUMPRODUCT((A10:A10000=$B$1)*(D10: D10000>=DATE(YEAR(TODAY()),1,1))*(D10: D10000<=DATE(YEAR(TODAY()),12,31)))

    or more efficiently

    =COUNTIFS(A10:A10000,$B$1,D10: D10000,">="&DATE(YEAR(TODAY()),1,1),D10: D10000,"<="&DATE(YEAR(TODAY()),12,31))


  7. #7
    Acolyte naridox's Avatar
    Join Date
    Feb 2017
    Location
    NCR
    Posts
    30
    Articles
    0
    Excel Version
    2013
    Quote Originally Posted by NBVC View Post
    Try this:

    =SUMPRODUCT((A10:A10000=$B$1)*(D10: D10000>=DATE(YEAR(TODAY()),1,1))*(D10: D10000<=DATE(YEAR(TODAY()),12,31)))

    or more efficiently

    =COUNTIFS(A10:A10000,$B$1,D10: D10000,">="&DATE(YEAR(TODAY()),1,1),D10: D10000,"<="&DATE(YEAR(TODAY()),12,31))
    It worked! Thank you very much!
    Christian
    Information Designer
    National Capital Region

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •