Counting unique values with variables

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.

On Thu, 4 Aug 2005 13:47:01 -0700, "HoyaJane"
<[email protected]> wrote:

>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.
>

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!

In article <[email protected]>,
"HoyaJane" <[email protected]> wrote:

> 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.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban arte available to your workbook

=COUNTA(ArrayUniques(vlookups(1,A1:C7,3)))

Alan Beban

HoyaJane wrote:
> 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.
>
>