Counting unique ID#s with two filter criteria

[megnin]

column A contains Order ID#s with many duplicates
column b contains criteria #1 with many duplicates
column c contains criteria #2 with many duplicats

Example
A.....B.....C.....
10G...RED...Circle
10G...RED...Circle
10G...Green.Circle
10G...BLUE..Circle
11B...RED...Circle
11B...RED...Circle
11B...Pink..Triangle
11B...BLUE..Square
12C...BLUE..Square
12C...BLUE..Circle
12C...Green.Triangle
12C...RED...Square
13D...Green.Circle
14E...Pink..Square
15F...Red...Triangle
16G...Blue..Triangle

I have to pull unique ID#s of only RED or Blue Circles and Red or Blue Squares.

I need a formula to give 3 as the result with the above data.

This is for a worksheet with 42000+ rows and 40 columns.

I tried to do it with a pivot table but it gave every occurance of the ID# not unique ID#s.

I got my number once by sorting and deleting "shapes" then "colors" then finally using Advanced Filter to copy unique ID#s for what remained into another column the just "COUNTA"ing them, but I need to do this on a regular basis so that method is less than ideal.

Any help appreciated!!!!!!!

[Juan Pablo González]

Define an Advanced filter for that. You could also record a macro while you're at it, to make it faster the next times.

[Chris Davison]

=SUMPRODUCT((B2:B40001={"red","blue"})*(C2:C40001="circle"))+SUMPRODUCT((B2:B40001={"red","blue"})*(C2:C40001="square"))

I suspect this can be shortened...

also, if you notice performance degredation, I'd suggest also trying =DCOUNT

[Juan Pablo González]

Mhmm... I'm confused now... do you need only the number ? or the list ?

[Nimrod]

Here's an array formula that I think does what you want.

=count(IF(($A2:$A=A1)*($B2:$B10=B1)*($C2:$C10=C1),$D:$D))

This is an array formula so after pasting into a cell you need to press ctrl+Shift+enter.

A1 = value looking for in colA
B1 = "" "" "" Colb
C1 = "" "" "' colc

1)Note ranges can be changed to what ever you need. And A1-B1-C1 can be changed to another cell in other columns
2)All rows need to be same length
3)Last thing is that you need to enter the number one in the d column so that the column of 1's is same length as the other's




[ This Message was edited by: Nimrod on 2002-05-15 16:30 ]

[ This Message was edited by: Nimrod on 2002-05-15 16:33 ]

[Yogi Anand]

Use the DCOUNTA function
With your data in cells A1:C17, and criteria range as F1:G2 -- where F1 houses Color, G1 houses Shape, and in cells F2 and G2 you key in the color and shape of interest

ID Color Shape
10G RED Circle
10G RED Circle
10G Green Circle
10G BLUE Circle
11B RED Circle
11B RED Circle
11B Pink Triang
11B BLUE Square
12C BLUE Square
12C BLUE Circle
12C Green Triang
12C RED Square
13D Green Circle
14E Pink Square
15F Red Triang
16G Blue Triang

then the following formula

=DCOUNTA($A$1:$C$17,2,F1:G2)

gives me 4 for Red and Circle
gives me 1 for REd and Square
gives me 2 for Blue and Circle
gives me 2 for Blue and Square

Hope This Helps

Regards!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:10 ]

[Yogi Anand]

Use the DCOUNTA function
With your data in cells A1:C17, and criteria range as F1:G2 -- where F1 houses Color, G1 houses Shape, and in cells F2 and G2 you key in the color and shape of interest

if you need to include the ID number in the criteria, expand the criteria to range E1:G2, where E1 houses ID

Regards!

[Aladin Akyurek]

Given that you have a huge range, I'd suggest trading off space (memory) against time (speed). As the figure shows, you need to use an additional column for every combination of conditions/criteria that you might need. The formula in the additional column in this example is:

=((B2="Red")*((C2="Circle")+(C2="Square"))+(B2="blue")*((C2="Circle")+(C2="Square")))*(COUNTIF($A$2:A2,A2)=1)

The criteria can be put in cells of their own and used instead of constants like "Red", etc.

The formula in E2 sums the additional column:

=SUM(D:D)

Aladin

Microsoft Excel - aaMultCondCount Megnin.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
K9		=
*	A	B	C	D	E
1	Field1	Field2	Field3	*	*
2	10G	RED	Circle	:alert('=((B2="Red")*((C2="Circle")+(C2="Square"))+(B2="blue")*((C2="Circle")+(C2="Square")))*(COUNTIF($A$2:A2,A2)=1)')>1	:alert('=SUM(D:D)')>3
3	10G	RED	Circle	:alert('=((B3="Red")*((C3="Circle")+(C3="Square"))+(B3="blue")*((C3="Circle")+(C3="Square")))*(COUNTIF($A$2:A3,A3)=1)')>0	*
4	10G	Green	Circle	:alert('=((B4="Red")*((C4="Circle")+(C4="Square"))+(B4="blue")*((C4="Circle")+(C4="Square")))*(COUNTIF($A$2:A4,A4)=1)')>0	*
5	10G	BLUE	Circle	:alert('=((B5="Red")*((C5="Circle")+(C5="Square"))+(B5="blue")*((C5="Circle")+(C5="Square")))*(COUNTIF($A$2:A5,A5)=1)')>0	*
6	11B	RED	Circle	:alert('=((B6="Red")*((C6="Circle")+(C6="Square"))+(B6="blue")*((C6="Circle")+(C6="Square")))*(COUNTIF($A$2:A6,A6)=1)')>1	*
7	11B	RED	Circle	:alert('=((B7="Red")*((C7="Circle")+(C7="Square"))+(B7="blue")*((C7="Circle")+(C7="Square")))*(COUNTIF($A$2:A7,A7)=1)')>0	*
8	11B	Pink	Triangle	:alert('=((B8="Red")*((C8="Circle")+(C8="Square"))+(B8="blue")*((C8="Circle")+(C8="Square")))*(COUNTIF($A$2:A8,A8)=1)')>0	*
9	11B	BLUE	Square	:alert('=((B9="Red")*((C9="Circle")+(C9="Square"))+(B9="blue")*((C9="Circle")+(C9="Square")))*(COUNTIF($A$2:A9,A9)=1)')>0	*
10	12C	BLUE	Square	:alert('=((B10="Red")*((C10="Circle")+(C10="Square"))+(B10="blue")*((C10="Circle")+(C10="Square")))*(COUNTIF($A$2:A10,A10)=1)')>1	*
11	12C	BLUE	Circle	:alert('=((B11="Red")*((C11="Circle")+(C11="Square"))+(B11="blue")*((C11="Circle")+(C11="Square")))*(COUNTIF($A$2:A11,A11)=1)')>0	*
12	12C	Green	Triangle	:alert('=((B12="Red")*((C12="Circle")+(C12="Square"))+(B12="blue")*((C12="Circle")+(C12="Square")))*(COUNTIF($A$2:A12,A12)=1)')>0	*
13	12C	RED	Square	:alert('=((B13="Red")*((C13="Circle")+(C13="Square"))+(B13="blue")*((C13="Circle")+(C13="Square")))*(COUNTIF($A$2:A13,A13)=1)')>0	*
14	13D	Green	Circle	:alert('=((B14="Red")*((C14="Circle")+(C14="Square"))+(B14="blue")*((C14="Circle")+(C14="Square")))*(COUNTIF($A$2:A14,A14)=1)')>0	*
15	14E	Pink	Square	:alert('=((B15="Red")*((C15="Circle")+(C15="Square"))+(B15="blue")*((C15="Circle")+(C15="Square")))*(COUNTIF($A$2:A15,A15)=1)')>0	*
16	15F	Red	Triangle	:alert('=((B16="Red")*((C16="Circle")+(C16="Square"))+(B16="blue")*((C16="Circle")+(C16="Square")))*(COUNTIF($A$2:A16,A16)=1)')>0	*
17	16G	Blue	Triangle	:alert('=((B17="Red")*((C17="Circle")+(C17="Square"))+(B17="blue")*((C17="Circle")+(C17="Square")))*(COUNTIF($A$2:A17,A17)=1)')>0	*
Sheet2

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

[Aladin Akyurek]

On 2002-05-15 16:02, Juan Pablo G. wrote:
Mhmm... I'm confused now... do you need only the number ? or the list ?

Megnin wants a count. The formula that I used in the additional column can serve as criterion (put in criteria range) in Advanced Filter. You get a list whose size is the desired count.

Aladin

[Juan Pablo González]

Finally an answer... I did read the post again to notice that ! anyway, thanks Aladin.