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

[bananney]

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-Town" are only a portion of the cell and using the wildcard asterick doesn't give the correct answer. I am doing the Control-Shift-Enter but getting the result of 0.

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

<tbody>
</tbody>

 

[Doc Holliday]

Can you paste the formula you are using?

 

[Marcelo Branco]

Maybe this array formula

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Out-of-School",$D$2:$D$11)),MATCH($A$2:$A$11,$A$2:$A$11,0)),ROW($A$2:$A$11)-ROW($A$2)+1),1))

confirmed with Ctrl+Shift+Enter

M.

 

[bananney]

Maybe this array formula

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Out-of-School",$D$2:$D$11)),MATCH($A$2:$A$11,$A$2:$A$11,0)),ROW($A$2:$A$11)-ROW($A$2)+1),1))

confirmed with Ctrl+Shift+Enter

M.

Marcelo,
If I use your formula for this spreadsheet and then add data, wouldn't I have to extend the range of D2:D11 and A2:A11? Is there a formula to use without having to change it when data is added?

 

[bananney]

Can you paste the formula you are using?

Doc Holliday,
I'm using this...
SUM(IF(FREQUENCY(IF(D:D="*Out-of-School*",MATCH(D:D,D:D,0)),ROW(D:AD)-ROW(D1)+1),1))

 

[Marcelo Branco]

Marcelo,
If I use your formula for this spreadsheet and then add data, wouldn't I have to extend the range of D2:D11 and A2:A11? Is there a formula to use without having to change it when data is added?

Say you expect, in the future, have 1000 rows of data.

You can increase the ranges in the formula like:.
=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Out-of-School",$D$2:$D$1000)),MATCH($A$2:$A$1000,$A$2:$A$1000,0)),ROW($A$2:$A$1000)-ROW($A$2)+1),1))

Ctrl+Shift+Enter

It works even with blank rows.

M.