Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique values across two columns with multiple conditions
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique values across two columns with multiple conditions
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,)*MA TCH(School,School,)),MATCH(Class,Class,)*MATCH(Sch ool,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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique values across two columns with multiple condition
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,)*MA TCH(School,School,)),MATCH(Class,Class,)*MATCH(Sch ool,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,)*MA TCH(School,School,)),MATCH(Class,Class,)*MATCH(Sch ool,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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique values across two columns with multiple conditions
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 , 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique values across two columns with multiple conditions
Hello,
I suggest the array formula =Pfreq(Pfreq(C5:C10,A5:A10,B5:B10)) Pfreq you can find he http://www.sulprobil.com/html/pfreq.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter for unique values in multiple columns | Excel Discussion (Misc queries) | |||
Count unique values based on multiple criteria | Excel Discussion (Misc queries) | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
Advanced unique cell count with multiple conditions ... help! | Excel Worksheet Functions |