• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Unique Values Associated with Unique Values with Multiple Criteria

B

bananney

New Member
Using the table below, I want to count the race of the unique students who have a referral for Disrespect. Each student only gets counted once even if they have multiple referrals so the race will only be counted once. There are 4 unique students that have a referral for Disrespect. There should be 2 Ws, 1 B and 1 H for a behavior type of Disrespect.

[pre]
Code: 
Behavior Type	Student	Race
Disrespect	  1	W
Disrespect	  2	B
Disrespect	  1	W
Disrespect	  1	W
Disrespect	  3	H
Disrespect	  4	W
Disrespect	  2	B
Disrespect	  1	W
Disrespect	  3	H
Disrespect	  4	W
Unauthorized	  1	W
Unauthorized      1	W
Unauthorized 	  4	W
Unauthorized      1	W
Unauthorized	  3	H
[/pre]
 
Bananney


Why not put the data into a Pivot Table


Row labels: Race

Column labels: Behaviour

Sum Values: Count of Race
 
If I did my pivot table as you suggested, then it counts a unique student more than once. Each student can only be counted once.
 
Hi ,


Getting a count of unique values is explained here :


http://www.get-digital-help.com/2011/07/12/count-unique-distinct-values-that-meet-multiple-criteria-in-excel/


Narayan
 
Hi ,


Sure.


First let us define the following named ranges :

[pre]
Code: 
Behavior_Type   :  $A$2:$A$16

Student         :  $B$2:$B$16

Race            :  $C$2:$C$16
[/pre]
Now , if we want to know the answers to the following questions :


1. How many unique students were referred for "Disrespect" ?


=SUM(IF(Behavior_Type="Disrespect",1/COUNTIFS(Behavior_Type,"Disrespect",Student,Student)))


entered as an array formula , using CTRL SHIFT ENTER.


Note that the single criterion is used in the IF and the COUNTIFS ; the unique field is repeated in the COUNTIFS.


2. How many unique students , having race = "W" , were referred for "Disrespect" ?


We now have two criteria. The above formula is extended as follows :


=SUM(IF((Behavior_Type="Disrespect")*(Race="W"),1/COUNTIFS(Behavior_Type,"Disrespect",Race,"W",Student,Student)))


entered as an array formula , using CTRL SHIFT ENTER.


Note : The COUNTIFS function is available only in Excel 2007 and later versions


Narayan
 
THANKS, Narayan, for your help BUT I am getting a #NAME? error. I am using your exact formula and CTRL SHIFT ENTER but to no avail.
 
Bananney


A worked example of Narayans formulas is here: https://www.dropbox.com/s/v0ti7wz5ja3yxjc/Bananney.xlsx


What version of Excel are you using ?
 
Are there any sort of restrictions on the data names for the formulas to work? My behavior types are like "01-01 Behavior Detrimental to Learning". They are a combination of numbers and text and begin with numbers. I know the formula is not working because of something like this problem.
 
Hi ,


There is no problem with the behavior types , as long as your named range is valid ; Excel will not accept any invalid range name at the time of entry itself.


If you are getting a #NAME? error , it means that you have entered a named range in your formula , which you have not created using the Name Manager ; or it can be that you have entered the name wrongly e.g.


You create a named range called Data_Range , but while entering the formula , you use DataRange.


Narayan
 
I finally got back to my spreadsheet and your formula worked but I have run into another problem with the #NAME? error that really has me baffled. I am trying to find the number of students that have received a particular resolution. I am copying and pasting a portion of the spreadsheet here. Can you tell why the #NAME? error is coming up?

[pre]
Code: 
Behavior_Type	Resolution	Student			Number of Students	Resolution List
00 Continuation of Incident	01 - Student Conference/Warning	086366			#NAME?	01 - Student Conference/Warning
01-01 Behavior Detrimental To Learning	01 - Student Conference/Warning	070247			#NAME?	10 - In-School Suspension
01-01 Behavior Detrimental To Learning	01 - Student Conference/Warning	120471
01-01 Behavior Detrimental To Learning	01 - Student Conference/Warning	139976
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	073902
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	084260
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	084260
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	084260
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	090030
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	090030
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	070372
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	070342
01-01 Behavior Detrimental To Learning	10 - In-School Suspension	127215
[/pre]
 
Hi ,


From your posted data , I infer that the error is in the Number of Students column ; can you copy + paste the formula from a cell where you are getting this error ?


Narayan
 
The formula I have used is =SUM(IF(Resolution="01 - Student Conference/Warning",1/COUNTIF(Resolution,"01 - Student Conference/Warning",Student,Student))). I enter as an array formula , using CTRL SHIFT ENTER.
 
Hi ,


Two points :


1. The COUNTIF should actually be COUNTIFS , since you have multiple criteria ; so your formula should be :


=SUM(IF(Resolution="01 - Student Conference/Warning",1/COUNTIFS(Resolution,"01 - Student Conference/Warning",Student,Student)))


2. Can you check that you have the following named ranges declared :



Resolution


Student




Narayan
 
You must log in or register to reply here.
Back
Top