Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
\ditzman via OfficeKB.com\
 
Posts: n/a
Default 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
  #2   Report Post  
Domenic
 
Posts: n/a
Default

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

  #3   Report Post  
\ditzman via OfficeKB.com\
 
Posts: n/a
Default

Thanks! I believe it is working perfectly.

--
Message posted via http://www.officekb.com
  #4   Report Post  
\ditzman via OfficeKB.com\
 
Posts: n/a
Default

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
  #5   Report Post  
Domenic
 
Posts: n/a
Default

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!



  #6   Report Post  
\ditzman via OfficeKB.com\
 
Posts: n/a
Default

That did it. Thanks again Domenic! It works great!

--
Message posted via http://www.officekb.com
  #7   Report Post  
slc
 
Posts: n/a
Default


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

  #8   Report Post  
Domenic
 
Posts: n/a
Default


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

  #9   Report Post  
slc
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"COUNTU" function in Excel to count unique entries in a range WayneL Excel Worksheet Functions 20 September 19th 08 03:50 AM
Count Unique Values annie Excel Worksheet Functions 1 June 9th 05 07:19 AM
Count Unique in Pivot Table Nelson Excel Discussion (Misc queries) 6 April 10th 05 10:05 PM
Pivot Table Unique Count bsantona Excel Worksheet Functions 1 February 11th 05 10:27 PM
Count of unique items meeting condition Tim C Excel Worksheet Functions 1 November 12th 04 04:03 AM


All times are GMT +1. The time now is 09:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"