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

1. ## 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. ## Re: Excel function for distinct count based on dates match

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

3. ## 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))`

4. ## Re: Excel function for distinct count based on dates match

It worked, thank you Muz

5. ## Re: Excel function for distinct count based on dates match

Originally Posted by Aryatect
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. ## 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. ## Re: Excel function for distinct count based on dates match

Great, glad could help and thanks for the feedback!

8. ## Re: Excel function for distinct count based on dates match

Hello,

can this be done using VBA?