Excel function for distinct count based on dates match
Results 1 to 8 of 8

Thread: Excel function for distinct count based on dates match
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel function for distinct count based on dates match

    Hello,

    I have data set where order ID's get created for every purchase transaction, whenever there is a change in transaction same order id gets updated and forms a new row.
    Now i wanted to distinct count how many orders i received per calendar day where the calendar dates sits out side of the data set.

    Appreciate if you guys could help me with the solution. Many thanks.

    id creation time Calendar
    285 11/20/2018 11/20/2018 Output
    285 11/20/2018 11/21/2018 Calendar date Distinct ID's count
    286 11/20/2018 11/22/2018 11/20/2018 10
    286 11/20/2018 11/23/2018 11/21/2018 2
    286 11/20/2018 11/24/2018
    286 11/20/2018 11/25/2018
    286 11/20/2018 11/26/2018
    286 11/20/2018 11/27/2018
    287 11/20/2018 11/28/2018
    287 11/20/2018 11/29/2018
    287 11/20/2018 11/30/2018
    288 11/20/2018 12/1/2018
    290 11/20/2018 12/2/2018
    290 11/20/2018 12/3/2018
    290 11/20/2018 12/4/2018
    291 11/20/2018 12/5/2018
    291 11/20/2018 12/6/2018
    291 11/20/2018 12/7/2018
    292 11/20/2018 12/8/2018
    292 11/20/2018 12/9/2018
    292 11/20/2018 12/10/2018
    293 11/20/2018 12/11/2018
    293 11/20/2018 12/12/2018
    293 11/20/2018
    293 11/20/2018
    293 11/20/2018
    294 11/20/2018
    294 11/20/2018
    294 11/20/2018
    297 11/20/2018
    297 11/20/2018
    297 11/20/2018
    297 11/20/2018
    298 11/21/2018
    298 11/21/2018
    298 11/21/2018
    299 11/21/2018
    299 11/21/2018
    299 11/21/2018
    299 11/21/2018

  2. #2
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel function for distinct count based on dates match

    Please check if this works.

    Excel 2013/2016
    ABCD
    1IDCreation TimeCalendarDistinct ID's count
    228511/20/201811/20/201810
    328511/20/201811/21/20182

    Sheet1



    Array Formulas
    CellFormula
    D2{=SUM(IF(C2=$B$2:$B$41, 1/(COUNTIFS($B$2:$B$41, C2, $A$2:$A$41, $A$2:$A$41)), 0))}
    D3{=SUM(IF(C3=$B$2:$B$41, 1/(COUNTIFS($B$2:$B$41, C3, $A$2:$A$41, $A$2:$A$41)), 0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Press Ctrl+Shift+Enter to enter this formula.

    Muz
    Sam_D_Ben

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel function for distinct count based on dates match

    Hi, Just another option (without CSE) to put in D2 and copying it down:

    Code:
    =SUMPRODUCT((MATCH($A$2:$A$41,$A$2:$A$41,0)=(ROW($A$2:$A$41)-ROW($A$2)+1))*($B$2:$B$41=$C2))
    Last edited by Aryatect; Jul 30th, 2019 at 04:49 PM.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel function for distinct count based on dates match

    It worked, thank you Muz

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel function for distinct count based on dates match

    Quote Originally Posted by Aryatect View Post
    Hi, Just another option (without CSE) to put in D2 and copying it down:

    Code:
    =SUMPRODUCT((MATCH($A$2:$A$41,$A$2:$A$41,0)=(ROW($A$2:$A$41)-ROW($A$2)+1))*($B$2:$B$41=$C2))

    It worked like a charm, thank you.. Aryatect.

    what if in case my range is dynamic ? say A2:i, B2:j

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel function for distinct count based on dates match

    Never mind, I fixed the formula the way i needed. thanks much again both of you..

    =SUMPRODUCT((MATCH(INDIRECT("A"&SUM(1,1)&":A"&(COUNTA($A:$A))),INDIRECT("A"&SUM(1,1)&":A"&(COUNTA($A:$A))),0)=(ROW(INDIRECT("A"&SUM(1,1)&":A"&(COUNTA($A:$A))))-ROW($A$2)+1))*(INDIRECT("G"&SUM(1,1)&":G"&(COUNTA($A:$A)))=$Q2))

  7. #7
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel function for distinct count based on dates match

    Great, glad could help and thanks for the feedback!
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel function for distinct count based on dates match

    Hello,

    can this be done using VBA?

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
  •