Formula to unique count with criteria based on a given year

[goethe168]

I'm using Excel 2019, and I have a table with 3 columns ABC containing

Order date | Order ID | Customer name

I need a formula to count the unique orders per customer based on a given “year”.

So in E2 will be a customer’s name and in E3 I’ll enter a year say 2023, and in F3 should be a formula to count unique orders of that customer within the year 2023.

The order date in column A is in the date format of YYYY-MM-DD

Any help would be appreciated!

 

[BigBeachBananas]

--

 

[BigBeachBananas]

Someone might have a better solution, but mine required an addtional column. Excel 2019 posted a challenge as it did not have the UNIQUE() function.

Book1
	A	B	C	D	E	F	G
1	Order Date	Order ID	Customer name	Filtered ID
2	1/10/11	1	A	4		Year	2018
3	2/13/11	1	A	3		Customer Name	C
4	2/11/12	3	C	1		Unique Order Count	3
5	3/17/12	1	D	4
6	8/17/12	2	A	4
7	7/23/16	2	A
8	1/1/18	5	B
9	1/5/18	4	C
10	5/3/18	3	C
11	8/12/18	1	C
12	5/12/18	4	C
13	5/12/18	4	C
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=FILTER(B2:B13,(YEAR(A2:A13)=G2)*(C2:C13=G3))
G4	G4	=SUMPRODUCT(IF(D2:D13<>"",1/COUNTIF(D2:D13,D2:D13)))
Dynamic array formulas.

 

[Fluff]

Excel 2019 posted a challenge as it did not have the UNIQUE() function.

Nor does it have the Filter function.

 

[BigBeachBananas]

Nor does it have the Filter function.

Microsoft document states that it did and I remember using Filter() in 2019. Am I missing something?

 

[Fluff]

Am I missing something?

Filter does not, and never has, existed in 2019. Unfortunately it does not help when MS cannot even get right themselves on this page FILTER function - Microsoft Support
This MS site is more reliable Excel functions (alphabetical) - Microsoft Support

 

[BigBeachBananas]

Filter does not, and never has, existed in 2019. Unfortunately it does not help when MS cannot even get right themselves on this page FILTER function - Microsoft Support
This MS site is more reliable Excel functions (alphabetical) - Microsoft Support

hmm... It doesn't help for sure when the official source isn't correct. It makes sense that Excel2019 did not have access to the dynamic array so functions like FILTER, SORT, and UNIQUE...so it should be obvious that FILTER() isn't in the list.

Thanks for the info. Back to the drawing board.

 

[BigBeachBananas]

Okay, how about this using IF...

Book1.xlsx
	A	B	C	D	E	F	G
1	Order Date	Order ID	Customer name	Filtered OrderID
2	1/10/2011	1	A			Year	2018
3	2/13/2011	1	A			Customer Name	C
4	2/11/2012	3	C			Unique Order Count	3
5	3/17/2012	1	D
6	8/17/2012	2	A
7	7/23/2016	2	A
8	1/1/2018	5	B
9	1/5/2018	4	C	4
10	5/3/2018	3	C	3
11	8/12/2018	1	C	1
12	5/12/2018	4	C	4
13	5/12/2018	4	C	4
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=IF((YEAR(A2:A13)=G2)*(C2:C13=G3)=1,B2:B13,"")
G4	G4	=SUMPRODUCT(IF(D2:D13<>"",1/COUNTIF(D2:D13,D2:D13)))
Dynamic array formulas.

 

[goethe168]

Thanks for all your replies!

I'VE managed to figure out a few array formulae as below, which worked. However, the problem is that they don't work on a huge data set


=SUM(IF(($C$2:$C$5=E2)*(YEAR($A$2:$A$5)=E3),(1/COUNTIFS($C$2:$C$5,$C$2:$C$5,$B$2:$B$5,$B$2:$B$5)),0))

=SUM(IF(FREQUENCY(IF((C2:C5=E2)*(YEAR($A$2:$A$5)=E3),B2:B5),B2:B5),1))