|
count unique with conditions
I am looking for a function or group of functions that can help me count the
unique entries that meet two other criteria. The table is similar to: Column A: Column B: Column C: Column D: 12346 Level1 ACME 345334 12346 Level1 ACME 34523 12346 Level2 ACME 34622 12376 Level2 ACME 3453 13255 Level2 DODAD 345 13548 Level1 THINGABOB 322562 12356 Level1 WIDGET 34622 12376 Level2 WIDGET 3453 12378 Level3 WIDGET 546443 Column A is a list of unknown ID numbers. Column B&C values are known. The table is sorted by Column C, then Column B, then Column A, all in acending order. What I want is to be able to look for a value in Column C & Column B, and then count the unique items in Column A that matches with Column B & C. Example: If I search for "ACME" & "LEVEL1" I should get a result of 1. If I search for "ACME" & "LEVEL2" I should get a result of 2. I do not need to know which ones are unique, I just need to know the total of unique items as long as the line meets the other two criteria. I'm not sure if this is even possible. I'm using Excel 2000. Thanks for any help you can give me!! -- Message posted via http://www.officekb.com |
Try...
=SUM(IF(FREQUENCY(IF((B1:B9="Level1")*(C1:C9="Acme "),A1:A9),IF((B1:B9="Le vel1")*(C1:C9="Acme"),A1:A9))0,1,0)) or =COUNT(1/FREQUENCY(IF((B1:B9="Level1")*(C1:C9="Acme"),A1:A9 ),IF((B1:B9="L evel1")*(C1:C9="Acme"),A1:A9))) Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , \"ditzman via OfficeKB.com\" wrote: I am looking for a function or group of functions that can help me count the unique entries that meet two other criteria. The table is similar to: Column A: Column B: Column C: Column D: 12346 Level1 ACME 345334 12346 Level1 ACME 34523 12346 Level2 ACME 34622 12376 Level2 ACME 3453 13255 Level2 DODAD 345 13548 Level1 THINGABOB 322562 12356 Level1 WIDGET 34622 12376 Level2 WIDGET 3453 12378 Level3 WIDGET 546443 Column A is a list of unknown ID numbers. Column B&C values are known. The table is sorted by Column C, then Column B, then Column A, all in acending order. What I want is to be able to look for a value in Column C & Column B, and then count the unique items in Column A that matches with Column B & C. Example: If I search for "ACME" & "LEVEL1" I should get a result of 1. If I search for "ACME" & "LEVEL2" I should get a result of 2. I do not need to know which ones are unique, I just need to know the total of unique items as long as the line meets the other two criteria. I'm not sure if this is even possible. I'm using Excel 2000. Thanks for any help you can give me!! |
|
I have one more question. If Column B was slightly modified:
Column A: Column B: Column C: Column D: 12346 Level1a ACME 345334 12346 Level1b ACME 34523 12346 Level2 ACME 34622 12376 Level2 ACME 3453 13255 Level2 DODAD 345 13548 Level1a THINGABOB 322562 12356 Level1b WIDGET 34622 12376 Level2 WIDGET 3453 12378 Level3 WIDGET 546443 Is there a way to do the same thing, but treat Level1a and Level1b as the same? So I can search for Acme, and Level1a and Level1b and still come up with a result of 1? I tried using "Level1*" in my function but it does not like that. Thanks again! -- Message posted via http://www.officekb.com |
Try...
=SUM(IF(FREQUENCY(IF((LEFT(B1:B9,6)="Level1")*(C1: C9="Acme"),A1:A9),IF((L EFT(B1:B9,6)="Level1")*(C1:C9="Acme"),A1:A9))0,1, 0)) or =COUNT(1/FREQUENCY(IF((LEFT(B1:B9,6)="Level1")*(C1:C9="Acme "),A1:A9),IF(( LEFT(B1:B9,6)="Level1")*(C1:C9="Acme"),A1:A9))) Both formulas need to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , \"ditzman via OfficeKB.com\" wrote: I have one more question. If Column B was slightly modified: Column A: Column B: Column C: Column D: 12346 Level1a ACME 345334 12346 Level1b ACME 34523 12346 Level2 ACME 34622 12376 Level2 ACME 3453 13255 Level2 DODAD 345 13548 Level1a THINGABOB 322562 12356 Level1b WIDGET 34622 12376 Level2 WIDGET 3453 12378 Level3 WIDGET 546443 Is there a way to do the same thing, but treat Level1a and Level1b as the same? So I can search for Acme, and Level1a and Level1b and still come up with a result of 1? I tried using "Level1*" in my function but it does not like that. Thanks again! |
|
I have the similar situation except that Col A is not number but text. Tried both formulas (the ones without the LEFT function) but they returned 0 if Col A is text (not number). Can anyone help to advise how to amend the formula for this case. Thanks Column A: Column B: Column C: Column D: AP1 Level1a ACME 345334 AP1 Level1b ACME 34523 AP1 Level2 ACME 34622 AP2 Level2 ACME 3453 AP4 Level2 DODAD 345 AP3 Level1a THINGABOB 322562 AP5 Level1b WIDGET 34622 AP2 Level2 WIDGET 3453 AP6 Level3 WIDGET 546443 -- slc ------------------------------------------------------------------------ slc's Profile: http://www.excelforum.com/member.php...fo&userid=1439 View this thread: http://www.excelforum.com/showthread...hreadid=384938 |
Try... =SUM(IF(FREQUENCY(IF((A1:A9<"")*(LEFT(B1:B9,6)="L evel1")*(C1:C9="Acme"),MATCH(A1:A9,A1:A9,0)),ROW(I NDEX(A1:A9,0,0))-ROW(A1)+1)0,1,0)) or =COUNT(1/FREQUENCY(IF((A1:A9<"")*(LEFT(B1:B9,6)="Level1")* (C1:C9="Acme"),MATCH(A1:A9,A1:A9,0)),ROW(INDEX(A1: A9,0,0))-ROW(A1)+1)) Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! slc Wrote: I have the similar situation except that Col A is not number but text. Tried both formulas (the ones without the LEFT function) but they returned 0 if Col A is text (not number). Can anyone help to advise how to amend the formula for this case. Thanks Column A: Column B: Column C: Column D: AP1 Level1a ACME 345334 AP1 Level1b ACME 34523 AP1 Level2 ACME 34622 AP2 Level2 ACME 3453 AP4 Level2 DODAD 345 AP3 Level1a THINGABOB 322562 AP5 Level1b WIDGET 34622 AP2 Level2 WIDGET 3453 AP6 Level3 WIDGET 546443 -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=384938 |
Domenic, Thanks. -- slc ------------------------------------------------------------------------ slc's Profile: http://www.excelforum.com/member.php...fo&userid=1439 View this thread: http://www.excelforum.com/showthread...hreadid=384938 |
| All times are GMT +1. The time now is 03:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com