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

Need Formula to Count Unique Number in a Column Using Partial Criteria

B

bananney

New Member
I am trying to get a formula that will return the following result. If column D shows "Out-of-School" anywhere in the cell, then I want to count the number of unique students that have a resolution stating "out-of-school". For this particular set of data there are 3 unique students that have a resolution of "out-of-school". The problem I have is that the words "Out-of-School" are only a portion of the cell and using the wildcard asterisk doesn't give the correct answer. I am doing the Control-Shift-Enter but getting the result of 0.

[pre]
Code: 
Stu	Last	First	Resolution
594	Arm	Lance	Loss of Privilege
567	West	Kayne	Short-Term Out-of-School Suspension
567	West	Kayne	In-School Suspension
345	Poe	Amy	Parent Contact/Conference
105	Hope	Bob	Student Conference/Warning
210	Fey	Tina	Parent Contact/Conference
224	Dal	Jeff	Long Term Out-of-School Suspension
224	Dah	Jeff	Out-of-School Suspension Pending Hearing
580	Kar	Kim	Short-Term Out-of-School Suspension
52	Hard	Tonya	Detention
[/pre]
 
Hi ,


This is a continuation of your previous topic ! Try this :


=SUM(IF(ISNUMBER(FIND("Out-of-School",Resolution)),1/COUNTIFS(Resolution,"*Out-of-School*",Stu,Stu)))


Enter this as an array formula , using CTRL SHIFT ENTER.


Narayan
 
Yes I am new at this Excell stuff.


So here is my formula:

SUM(IF(ISNUMBER(FIND("Out-of-School",D:D)),1/COUNTIFS(D:D,"*Out-of-School*",A:A,A:A)))


I am getting a error of division by zero. What am I doing wrong?
 
Hi ,


When you use entire column references , the blank cells give rise to the #DIV/0! error ; as far as possible , try to avoid such column references ; use the Name Manager to create named ranges which will be dynamic , and grow with your data.


You can define a named range called Resolution , and use the following formula in the Refers To box :


=Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNTA(Sheet1!$D:$D)-1)


Sheet1!$D$2 is the starting point of your data , and I assume that your data has a heading in row 1 , and there are no blank rows in between your data.


Similarly , create another named range called Stu or Student , using the same formula as above , and changing all the references to refer to cells in column A.


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