In article om,
"Harlan Grove" wrote:

ellebelle wrote...
I have a list of data.

Mike JOB1
Karen JOB2
Unfilled JOB1
Unfilled JOB2
Unfilled JOB1
Alex JOB1
Alex JOB1

I'll assume this is a 2-column range with defined name TBL.

I want to count the unique person that works on a job, however "unfilled"
always denotes a new person and therefore I do not want it to be unique. eg.
searching for "JOB 1" should give me 4 because i do not want to count Alex
twice but I do want to count Unfilled twice.

No ancillary cells needed. If the job code sought were entered in a
cell named JOB, try the following array formula.

=COUNT(1/(INDEX(TBL,0,2)=JOB)/(IF(INDEX(TBL,0,1)="Unfilled",ROW(TBL),
MATCH(INDEX(TBL,0,1),INDEX(TBL,0,1),0)-1+MIN(ROW(TBL))=ROW(TBL))))

If the data can contain the following...

Mike JOB1
Karen JOB2
Unfilled JOB1
Unfilled JOB2
Unfilled JOB1
Alex JOB2 <---
Alex JOB1 <---

....I believe the unique count should be 4. If this is correct, the
above formula will fail and return 3. Similar to the above formula,
mine can be shortened to...

=COUNT(1/FREQUENCY(IF(B$2:B$8=D2,IF(A$2:A$8="Unfilled",ROW( A$2:A$8)-ROW(A
$2)+1,MATCH(A$2:A$8,A$2:A$8,0))),ROW(A$2:A$8)-ROW(A$2)+1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!