Counting unique values with variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a similar question, except, I need to count unique instances where
there is another variable. Sample Data:

Program Student University
1 John North University
1 Steven North University
1 James South University
2 Susie Case University
2 Laura West University
2 Lisa North University

So, in this sample data, I would need to count the number of unique
universities per program, that is, Program 1 - 2 univeristies, program 2 - 3
universities.

Additionally, I need to ensure that the formula updates upon opening, since
the data set is in another sheet, and will be updated frequently.
 
I have a similar question, except, I need to count unique instances where
there is another variable. Sample Data:

Program Student University
1 John North University
1 Steven North University
1 James South University
2 Susie Case University
2 Laura West University
2 Lisa North University

So, in this sample data, I would need to count the number of unique
universities per program, that is, Program 1 - 2 univeristies, program 2 - 3
universities.

Additionally, I need to ensure that the formula updates upon opening, since
the data set is in another sheet, and will be updated frequently.
Click to expand...

Here's one way.

1. Download and install Longre's morefunc.xll from http://xcell05.free.fr/
2. With the ranges named Program & University (e.g: Program:= A1:A1000;
University := C1:C1000) use the *array* formula:

=COUNTDIFF(IF(Program=Num,University),,FALSE)

Num is the Program number or a cell reference containing the program number.

To enter an *array* formula, after copying or pasting the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.



--ron
 
Here's another way...

=SUM(IF(FREQUENCY(IF(($A$2:$A$7=1)*($C$2:$C$7<>""),MATCH($C$2:$C$7,$C$2:$
C$7,0)),ROW($C$2:$C$7)-ROW($C$2)+1)>0,1,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Back
Top