Incremental count of unique values from a dynamically generated list.

[vampsthevampyre]

Evening All,

Can anybody help I'm looking to create a formula that will count unique sector values. I have currently produced the correct output using COUNTIF as highlighted in column N.

Is there a formula that will dynamically create the values in column N with out dragging the formula from N3 down to N28.

Any help would be appreciated

Regards

Ian

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Data				ID	Catagories		ID	Ticker	Sector	Sector ID		COUNTIF
3		GSK	Healthcare			1	Basic Materials		1	RIO	Basic Materials	1		1
4		DCC	Energy			2	Communication Services		2	GLEN	Basic Materials	1		2
5		KETL	Technology			3	Consumer Cyclical		3	MONY	Communication Services	2		1
6		UU.	Utilities			4	Consumer Defensive		4	VOD	Communication Services	2		2
7		NG.	Utilities			5	Energy		5	TND	Consumer Cyclical	3		1
8		CGS	Industrials			6	Financial		6	PSN	Consumer Cyclical	3		2
9		MGNS	Industrials			7	Healthcare		7	VTY	Consumer Cyclical	3		3
10		TND	Consumer Cyclical			8	Industrials		8	BME	Consumer Defensive	4		1
11		PSN	Consumer Cyclical			9	Real Estate		9	DGE	Consumer Defensive	4		2
12		VTY	Consumer Cyclical			10	Technology		10	ULVR	Consumer Defensive	4		3
13		BME	Consumer Defensive			11	Utilities		11	BATS	Consumer Defensive	4		4
14		DGE	Consumer Defensive						12	DCC	Energy	5		1
15		ULVR	Consumer Defensive						13	LGEN	Financial	6		1
16		BATS	Consumer Defensive						14	PHNX	Financial	6		2
17		LMP	Real Estate						15	MNG	Financial	6		3
18		SRE	Real Estate						16	CSN	Financial	6		4
19		PHP	Real Estate						17	GSK	Healthcare	7		1
20		THRL	Real Estate						18	CGS	Industrials	8		1
21		RIO	Basic Materials						19	MGNS	Industrials	8		2
22		GLEN	Basic Materials						20	LMP	Real Estate	9		1
23		MONY	Communication Services						21	SRE	Real Estate	9		2
24		VOD	Communication Services						22	PHP	Real Estate	9		3
25		LGEN	Financial						23	THRL	Real Estate	9		4
26		PHNX	Financial						24	KETL	Technology	10		1
27		MNG	Financial						25	UU.	Utilities	11		1
28		CSN	Financial						26	NG.	Utilities	11		2
29
Sheet1

Cell Formulas
Range	Formula
F3:F13,I3:I28	F3	=SEQUENCE(MAX(COUNTA(G3#)))
G3:G13	G3	=SORT(UNIQUE(C3:C28),,1)
J3:J28	J3	=SORTBY(B3:B28,C3:C28,1)
K3:K28	K3	=XLOOKUP(J3#,B3:B28,C3:C28,"missing",0)
L3:L28	L3	=XLOOKUP(K3#,G3#,F3#,"Missing",0)
N3:N28	N3	=COUNTIF($L$3:L3,L3)
Dynamic array formulas.

 

[Fluff]

How about

=LET(r,ROWS(L3#),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(L3#=TOROW(L3#)),SEQUENCE(r,,,0)))

 

[Eric W]

Maybe:

=SEQUENCE(ROWS(L3#),,2)-MATCH(L3#,L3#,0)

 

[vampsthevampyre]

Wow thanks @Fluff and @Eric W , two excellent solutions it still amazes me how you come up with these elegant solutions. Just a shame I cannot mark both up as solutions so I gave it to the first with the solution.

Thanks once again
Ian

 

[Peter_SSs]

As an aside: Wondering why you have the MAX() function in the F3 formula (since the max of a single number will be that number)?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[vampsthevampyre]

As an aside: Wondering why you have the MAX() function in the F3 formula (since the max of a single number will be that number)?

Because I'm an idiot? I'm hoping its been left over from another equation I have copied and just not noticed, but I do expect my initial answer is correct