I have a large spread sheet and I want to know the total number of classes
for an area. Classes with the same title but at different HS need to be
counted seperately. For Example:
School Class Area
John HS Algebra Math
John HS Geometry Math
John HS Genetics Science
Jane HS Genetics Science
Jane HS Accounting Business
Jane HS Accounting Business

Total Unique Math Classes = 2
Total Unique Science Classes = 2
Total Unique Business Classes = 1

Thanks in advance.

School, Class, and Area are named ranges

Criteria:
E1: holds Math
E2: holds Science
E3: holds Business

In F2:
=SUM(N(FREQUENCY(IF(Area=E2,MATCH(Class,Class,)*MATCH(School,School,)),MATCH(Class,Class,)*MATCH(School,School,))>0))

ctrl+shift+enter, not just enter
copy down



"Ert" wrote:

> I have a large spread sheet and I want to know the total number of classes
> for an area. Classes with the same title but at different HS need to be
> counted seperately. For Example:
> School Class Area
> John HS Algebra Math
> John HS Geometry Math
> John HS Genetics Science
> Jane HS Genetics Science
> Jane HS Accounting Business
> Jane HS Accounting Business
>
> Total Unique Math Classes = 2
> Total Unique Science Classes = 2
> Total Unique Business Classes = 1
>
> Thanks in advance.
>

Correction:
School, class, and Area are Defined name ranges

Criteria
E2: holds Math
E3: holds Science
E4: holds Business

In F2:
=SUM(N(FREQUENCY(IF(Area=E2,MATCH(Class,Class,)*MATCH(School,School,)),MATCH(Class,Class,)*MATCH(School,School,))>0))

ctrl+shift+enter, not just enter
copy down


"Teethless mama" wrote:

> School, Class, and Area are named ranges
>
> Criteria:
> E1: holds Math
> E2: holds Science
> E3: holds Business
>
> In F2:
> =SUM(N(FREQUENCY(IF(Area=E2,MATCH(Class,Class,)*MATCH(School,School,)),MATCH(Class,Class,)*MATCH(School,School,))>0))
>
> ctrl+shift+enter, not just enter
> copy down
>
>
>
> "Ert" wrote:
>
> > I have a large spread sheet and I want to know the total number of classes
> > for an area. Classes with the same title but at different HS need to be
> > counted seperately. For Example:
> > School Class Area
> > John HS Algebra Math
> > John HS Geometry Math
> > John HS Genetics Science
> > Jane HS Genetics Science
> > Jane HS Accounting Business
> > Jane HS Accounting Business
> >
> > Total Unique Math Classes = 2
> > Total Unique Science Classes = 2
> > Total Unique Business Classes = 1
> >
> > Thanks in advance.
> >

Assuming that A2:C7 contains the data, and E2:E4 contains Math,
Science, and Business, try...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF($C$2:$C$7=E2,MATCH("~"&$A$2:$A$7&$B$2:$B$7,$A$2:$A$7
&$B$2:$B$7&"",0)),ROW($C$2:$C$7)-ROW($C$2)+1),1))

Hope this helps!

In article <625660E1-E37B-47C7-BA64-(E-Mail Removed)>,
Ert <(E-Mail Removed)> wrote:

> I have a large spread sheet and I want to know the total number of classes
> for an area. Classes with the same title but at different HS need to be
> counted seperately. For Example:
> School Class Area
> John HS Algebra Math
> John HS Geometry Math
> John HS Genetics Science
> Jane HS Genetics Science
> Jane HS Accounting Business
> Jane HS Accounting Business
>
> Total Unique Math Classes = 2
> Total Unique Science Classes = 2
> Total Unique Business Classes = 1
>
> Thanks in advance.

Hello,

I suggest the array formula
=Pfreq(Pfreq(C5:C10,A5:A10,B5:B10))

Pfreq you can find here:
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd