Unique Records Search

[Jon Johny]

I have a formula for counting unique text records. However, is it possible to count unioque records based on certain conditions. For example, I have a stats sheet for a sports league and I want to count all the unique teams for dsivision 'North'. Coloumn A has the division, coloumn B has the team they played on that day.

I want to count all the unique records in column B, but based on those teams being in the 'North Division' (column A)

I want to do this with Excel, and not in VBA.

My formula for finding unique records is:

 

[Aladin Akyurek]

I'm working on this formula with dynamic ranges and its not accepting. Named ranges yes, but not dynamic. If you want I can send you my example spreadsheet. Shoot me a private message with your email...

Lets assume that the formula you invoke is:

=SUM(IF(FREQUENCY(IF(A$1:A$15=C1,IF(B$1:B$15<>"",MATCH(B$1:B$15,B$1:B$15,0))),ROW(B$1:B$15)-ROW(B$1)+1),1))

Lets assume the following dynamic named ranges: Arange and Brange.

The formula becomes:

=SUM(IF(FREQUENCY(IF(Arange=C1,IF(Brange<>"",MATCH(Brange,Brange,0))),ROW(Brange)-ROW(INDEX(Brange,1,1))+1),1))

 

[T. Valko]

I'm working on this formula with dynamic ranges and its not accepting. Named ranges yes, but not dynamic. If you want I can send you my example spreadsheet. Shoot me a private message with your email...

How you define the dynamic range(s) depends on several factors.

Just sent you an email.