Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMPRODUCT/DATEDIF (2003)

    I am trying to figure out a formula array that will produce a count value looking at a value criteria and a date criteria. I want to accommodate a greater than and less date criteria. My end state is counting certain types of records that fall in specified date windows.

    The date criteria is custom formatted d-mmm-yy.

    =(SUMPRODUCT(($B$3:$B$23="Red")*(DATEDIF($C$3:$C23 >"30-Sep-07"))*(DATEDIF($C$3:$C23<1-Jan-08)))

    This obviously doesn't work, but it is almost there
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMPRODUCT/DATEDIF (2003)

    How about:
    =SUMPRODUCT(($B$3:$B$23="Red")*($C$3:$C23>DATE(200 7,9,30))*($C$3:$C23<DATE(2008,1,1)))

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMPRODUCT/DATEDIF (2003)

    Steve,
    Thanks. That worked well. I have added another count criteria to the formula array. I want to count by Organization (reference attached workbook), but use a wildcard or something like that.

    The primary organizations are "A-1" and "A-2", but each record has the organization defined more specifically--that is, some records have a sub-organization of "A-1" or "A-2", like "A-11" or "A-223".

    I would want the formula below to include *($D$3:$D$23="A-1*) or something like that assuming "*" can be used as a wildcard. Excel doesn't seem to want to accept that thought.

    Amy
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMPRODUCT/DATEDIF (2003)

    =SUMPRODUCT(($B$3:$B$23="Red")*($C$3:$C23>DATE(200 7,9,30))*($C$3:$C23<DATE(2008,1,1))*(LEFT($D$3:$D$ 23,3)="A-1"))

    You can not use a wildcard by Left(something,3)="A-1" is equivalent to "A-1*"

    Steve

Posting Permissions

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