Excel function for distinct count based on dates match

[samshaik]

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.

[TABLE="width: 0"]
<tbody>[TR]
[TD]id[/TD]
[TD]creation time[/TD]
[TD][/TD]
[TD][/TD]
[TD]Calendar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]285[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]Output[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]285[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]Calendar date[/TD]
[TD]Distinct ID's count[/TD]
[/TR]
[TR]
[TD]286[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/22/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/20/2018[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]286[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/23/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/21/2018[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]286[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/24/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]286[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/25/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]286[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/26/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]286[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/27/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]287[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/28/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]287[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/29/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]287[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/30/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]288[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/1/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]290[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/2/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]290[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/3/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]290[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/4/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]291[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/5/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]291[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/6/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]291[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/7/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]292[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/8/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]292[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/9/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]292[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]293[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]293[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]293[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]293[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]293[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]294[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]294[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]294[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]297[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]297[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]297[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]297[/TD]
[TD]11/20/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]298[/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]298[/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]298[/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]299[/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]299[/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]299[/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]299[/TD]
[TD]11/21/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Sam_D_Ben]

Please check if this works.

Excel 2013/2016
	A	B	C	D
1	ID	Creation Time	Calendar	Distinct ID's count
2	285	11/20/2018	11/20/2018	10
3	285	11/20/2018	11/21/2018	2
Sheet1

Cell Formulas
Range	Formula
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))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Press Ctrl+Shift+Enter to enter this formula.

Muz

 

[Aryatect]

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

=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))

 

[samshaik]

It worked, thank you Muz

 

[samshaik]

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

=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

 

[samshaik]

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))

 

[Aryatect]

Great, glad could help and thanks for the feedback!

 

[samshaik]

Hello,

can this be done using VBA?