"ellebelle" skrev i en meddelelse
...
I have a list of data.

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

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.

Any help appreciated,

Ellen



Hi Ellen

One way with a helper column:

Assuming data in A2:B100, and job to search for in E1.
In C2 enter A2&B2
Copy C2 down to C3:C100

In some cell:

=SUMPRODUCT((COUNTIF(OFFSET(C2:C100,,,ROW(C2:C100)-ROW(C2)+1),
C2:C100)=1)*(B2:B100=E1))+MAX(COUNTIF(C2:C100,"Unf illed"&E1)-1,0)

--
Best regards
Leo Heuser

Followup to newsgroup only please.