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))))