count unique with mulitple criteria

[Guest]

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

 

[Don Guillett]

one way

=SUMPRODUCT((B2:B22="job1")*1)-MIN(SUMPRODUCT((B2:B22="job1")*(A2:A22="unfilled")),1)

 

[Guest]

Try this

=SUMPRODUCT((A1:A7="Unfilled")*(B1:B7="JOB1"))+SUMPRODUCT((A1:A7<>"Unfilled")*(B1:B7="JOB1")*(1/COUNTIF(A1:A7,A1:A7)))

 

[Don Guillett]

tested?

 

[Bernie Deitrick]

Don,

This doesn't work with mutliples of Name/Job combinations.

Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ellen,

Using extra columns of helper formulas will work, given your criteria....

With your list in A2:B100

In C2, copied down to C100
=A2&B2
In D2, copied down to D100
=IF(AND(B2="Job1",COUNTIF($C$2:C2,C2)=1),C2,IF(AND(A2="Unfilled",B2="Job1"),C2,"NO"))

Then here's your desired result:
=COUNTA(D2100)-COUNTIF(D2100,"NO")

You can hide columns C and D or move them off to the right, if you want, to make the view less
cluttered....

HTH,
Bernie
MS Excel MVP

 

[Domenic]

Very expensive, but maybe...

Assuming that that A2:B8 contains the data, let D2 and D3 contain JOB1
and JOB2. Then try...

E2, copied down:

=SUM(IF(FREQUENCY(IF(B$2:B$8=D2,MATCH(A$2:A$8,A$2:A$8,0)+IF(A$2:A$8="Unfi
lled",ROW(A$2:A$8)/10^5)),IF(B$2:B$8=D2,MATCH(A$2:A$8,A$2:A$8,0)+IF(A$2:A
$8="Unfilled",ROW(A$2:A$8)/10^5))),1))

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER.

Hope this helps!

 

[Leo Heuser]

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,"Unfilled"&E1)-1,0)

 

[Harlan Grove]

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

 

[Domenic]

ellebelle wrote...

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


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!

 

[Guest]

Great job Domenic !

I learned a new trick from your formula...

Thanks !

 

[Bernie Deitrick]

Don,

" i do not want to count Alex twice"

Bernie

 

[Harlan Grove]

Domenic wrote...
....

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

....

Or mine could be corrected to

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

If I were to hardcode the ranges, it reduces to

=COUNT(1/($B2:$B8=D2)/IF($A2:$A8="Unfilled",1,MATCH($A2:$A8,
IF($B2:$B8=D2,$A2:$A8),0)-1+MIN(ROW($A2:$A8))=ROW($A2)))

 

[Guest]

Nice !

Isn't the last term of your "hardcoded" one wrong ?
A probable typo error, I think it should be ROW($A2:$A8) instead of
ROW($A2)...

 

[Guest]

Because it doesn't work as it is now ?

 

[Harlan Grove]

PapaDos wrote...
....

Isn't the last term of your "hardcoded" one wrong ?
A probable typo error, I think it should be ROW($A2:$A8) instead of
ROW($A2)...

....

Not a typo, a mistake. It should be

=COUNT(1/($B2:$B8=D2)/IF($A2:$A8="Unfilled",1,MATCH($A2:$A8,
IF($B2:$B8=D2,$A2:$A8),0)-1+ROW($A2)=ROW($A2:$A8)))

 

[Guest]

Thanks,
I think I understand your formulas, now.

I learned a few tricks in that thread.
Always a good thing...

Regards,
Luc

 

[Don Guillett]

Harlan almost never makes mistakes but when he does it is nice to point it
out.

 

[Guest]

Many thanks to everyone that has offered up solutions on this.
I've used the +COUNT(1/frequency.... formula and it does what I need.
Great stuff

 

[Domenic]

It should be...

=COUNT(1/($B2:$B8=D2)/IF($A2:$A8="Unfilled",1,MATCH($A2:$A8,
IF($B2:$B8=D2,$A2:$A8),0)-1+ROW($A2)=ROW($A2:$A8)))

I see it eliminates the need to use FREQUENCY. Nice one Harlan!