| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique with mulitple criteria
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
one way
=SUMPRODUCT((B2:B22="job1")*1)-MIN(SUMPRODUCT((B2:B22="job1")*(A2:A22="unfilled") ),1) -- Don Guillett SalesAid Software "ellebelle" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
Try this
=SUMPRODUCT((A1:A7="Unfilled")*(B1:B7="JOB1"))+SUM PRODUCT((A1:A7<"Unfilled")*(B1:B7="JOB1")*(1/COUNTIF(A1:A7,A1:A7))) "ellebelle" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
Don,
This doesn't work with mutliples of Name/Job combinations. Bernie MS Excel MVP "Don Guillett" wrote in message ... one way =SUMPRODUCT((B2:B22="job1")*1)-MIN(SUMPRODUCT((B2:B22="job1")*(A2:A22="unfilled") ),1) -- Don Guillett SalesAid Software "ellebelle" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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(D2:D100)-COUNTIF(D2:D100,"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 "ellebelle" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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! In article , ellebelle wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
"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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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)))) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
Great job Domenic !
I learned a new trick from your formula... Thanks ! -- Festina Lente "Domenic" wrote: 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! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
Don,
" i do not want to count Alex twice" Bernie I thought the question was for a total of all jobs but only counting unfilled once. So, where does is fail?? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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))) |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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)... -- Festina Lente "Harlan Grove" wrote: 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))) |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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))) |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
Thanks,
I think I understand your formulas, now. I learned a few tricks in that thread. Always a good thing... Regards, Luc -- Festina Lente "Harlan Grove" wrote: 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))) |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
Harlan almost never makes mistakes but when he does it is nice to point it
out. -- Don Guillett SalesAid Software "PapaDos" wrote in message ... Because it doesn't work as it is now ? -- Festina Lente "Don Guillett" wrote: I think it should why? -- Don Guillett SalesAid Software "PapaDos" wrote in message ... Nice ! Isn't the last term of your "hardcoded" one wrong ? A probable typo error, be ROW($A2:$A8) instead of ROW($A2)... -- Festina Lente "Harlan Grove" wrote: 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))) |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
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 "Harlan Grove" wrote: 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))) |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
In article .com,
"Harlan Grove" wrote: 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! |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
I thought the question was for a total of all jobs but only counting
unfilled once. So, where does is fail?? -- Don Guillett SalesAid Software "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Don, This doesn't work with mutliples of Name/Job combinations. Bernie MS Excel MVP "Don Guillett" wrote in message ... one way =SUMPRODUCT((B2:B22="job1")*1)-MIN(SUMPRODUCT((B2:B22="job1")*(A2:A22="unfilled") ),1) -- Don Guillett SalesAid Software "ellebelle" wrote in message ... 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 |
|
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
Ahhhhhhhhhhhhhhhh, I read it the other way.
-- Don Guillett SalesAid Software "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Don, " i do not want to count Alex twice" Bernie I thought the question was for a total of all jobs but only counting unfilled once. So, where does is fail?? |
|
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count unique with mulitple criteria
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
| Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria | Excel Worksheet Functions | |||
| how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
| How do I count wildcard text meeting certain criteria in EXCEL? | Excel Worksheet Functions | |||
| Count of unique items meeting condition | Excel Worksheet Functions | |||
Linear Mode
