|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#11
October 7th, 2006, 07:38 PM
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
October 7th, 2006, 09:14 PM
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
October 9th, 2006, 09:52 PM
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
October 9th, 2006, 10:45 PM
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
October 10th, 2006, 12:37 AM
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
October 10th, 2006, 01:30 AM
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
October 10th, 2006, 01:58 AM
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 news  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
October 10th, 2006, 05:21 PM
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
October 10th, 2006, 09:14 PM
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
