Incremental count of unique values from a dynamically generated list.

vampsthevampyre

New Member
Joined
Apr 29, 2016
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1
2DataIDCatagoriesIDTickerSectorSector IDCOUNTIF
3GSKHealthcare1Basic Materials1RIOBasic Materials11
4DCCEnergy2Communication Services2GLENBasic Materials12
5KETLTechnology3Consumer Cyclical3MONYCommunication Services21
6UU.Utilities4Consumer Defensive4VODCommunication Services22
7NG.Utilities5Energy5TNDConsumer Cyclical31
8CGSIndustrials6Financial6PSNConsumer Cyclical32
9MGNSIndustrials7Healthcare7VTYConsumer Cyclical33
10TNDConsumer Cyclical8Industrials8BMEConsumer Defensive41
11PSNConsumer Cyclical9Real Estate9DGEConsumer Defensive42
12VTYConsumer Cyclical10Technology10ULVRConsumer Defensive43
13BMEConsumer Defensive11Utilities11BATSConsumer Defensive44
14DGEConsumer Defensive12DCCEnergy51
15ULVRConsumer Defensive13LGENFinancial61
16BATSConsumer Defensive14PHNXFinancial62
17LMPReal Estate15MNGFinancial63
18SREReal Estate16CSNFinancial64
19PHPReal Estate17GSKHealthcare71
20THRLReal Estate18CGSIndustrials81
21RIOBasic Materials19MGNSIndustrials82
22GLENBasic Materials20LMPReal Estate91
23MONYCommunication Services21SREReal Estate92
24VODCommunication Services22PHPReal Estate93
25LGENFinancial23THRLReal Estate94
26PHNXFinancial24KETLTechnology101
27MNGFinancial25UU.Utilities111
28CSNFinancial26NG.Utilities112
29
Sheet1
Cell Formulas
RangeFormula
F3:F13,I3:I28F3=SEQUENCE(MAX(COUNTA(G3#)))
G3:G13G3=SORT(UNIQUE(C3:C28),,1)
J3:J28J3=SORTBY(B3:B28,C3:C28,1)
K3:K28K3=XLOOKUP(J3#,B3:B28,C3:C28,"missing",0)
L3:L28L3=XLOOKUP(K3#,G3#,F3#,"Missing",0)
N3:N28N3=COUNTIF($L$3:L3,L3)
Dynamic array formulas.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
How about
Excel Formula:
=LET(r,ROWS(L3#),MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(L3#=TOROW(L3#)),SEQUENCE(r,,,0)))
 
Upvote 1
Solution
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
 
Upvote 0
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)?
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
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 :ROFLMAO:
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top