Counting unique occurrences in Col B based on Col A as Criteria (Indefinite)

[Raghav]

Hi All,


My first post. Lets take below table as an example.


Col A Col B

ABC 123

DEF 245

ABC 234

ABC 456

ABC 234

ABC 123

DEF 657

DEF 657

DEF 245


I have a sheet where I have 2 columns where I need to get the unique number of occurences in Col B for a specific value in Col A (say "ABC"). I found solution to this but it has a finite range based on array function (A2:A10, B2:B10). But the challenge here that I face is that the number of entries in the table is not finite. So, if and when an user adds another entry at cell A11/B11 in the table for same ABC, that should get included.


This is what I used. This works when I know the entries.

SUM(IF(FREQUENCY(IF(A2:B10="ABC",MATCH(B2:B10,B2:B10,0)),ROW(A1:AB10)-ROW(A2)+1),1))


Could someone help?

 

[shrivallabha]

Welcome to Chandoo.org forums.


One way:

Goto formulas | Defined Names | Name manager


Define two names:

Name: LastA

Refers To: =INDEX(Sheet1!$A:$A,MATCH("z",Sheet1!$A:$A))


Name: LastB

Refers To: =INDEX(Sheet1!$B:$B,MATCH(9E+307,Sheet1!$B:$B))


You may have to adjust the Sheet Name to suit. And then alter the formula posted above as below:

=SUM(IF(FREQUENCY(IF(A2:LastA="ABC",MATCH(B2:LastB,B2:LastB,0)),ROW(A1:LastA)-ROW(A2)+1),1))