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. |
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. |
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. |
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. |
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 |
All times are GMT +1. The time now is 03:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com