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.

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

<tbody>
</tbody>

 

[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?