Counting unique entries with criteria
 

Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result here.

Thanks for any help...

Counting unique entries with criteria
 

=SUMPRODUCT(--($A$1:$A$6="dog")*($B$1:$C$6<""))


"Rachel" wrote:

Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result here.

Thanks for any help...

Counting unique entries with criteria
 

"Rachel" wrote:

Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result here.

Thanks for any help...


This is a variation of the COUNT for a specific logical result.

Check out this response and see if you can extrapolate it to cover your
particular situation ...

http://www.microsoft.com/office/comm...fad9&p=1&ntf=1

B+
HALinNY

Counting unique entries with criteria
 

=sumproduct((a2:a22="dog")*(b2:b22=0)*(c2:c22=3))

--
Don Guillett
SalesAid Software

"Rachel" wrote in message
...
Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result
here.

Thanks for any help...

Counting unique entries with criteria
 

=SUM(--(FREQUENCY(IF((B1:B6=0)*(C1:C6=3),MATCH(A1:A6,A1:A 6,0)),ROW(INDIRECT(
"1:"&ROWS(A1:A6))))0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rachel" wrote in message
...
Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result
here.

Thanks for any help...

Counting unique entries with criteria
 

With Pivot Table and one simple formula you will get the
number of unique entries for all possible combinations of criteria.
Assume your three columns have headers An, Bn and Cn.
Pivot Table Layout: Row = An; Column = Bn, Cn; Data = Count of An.
PT Options: Uncheck grand totals.
The PT, located at say A15, will look like this:

0 1
An 3 4 3 4
cat 2
dog 1
dogs 1
mice 1
mouse 1

unique 2 1 1 1

Add the row "unique" with this formula:
=COUNTA(B18:B22)

Counting unique entries with criteria
 

=COUNT( 1 / IF( ( $B$1:$B$6 = 0 ) * ( $C$1:$C$6 = 3 ), MATCH( $A$1:$A$6,
$A$1:$A$6, 0 ) = ( ROW( $A$1:$A$6 ) - ROW( $A$1 ) + 1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Rachel" wrote:

Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result here.

Thanks for any help...

Counting unique entries with criteria
 

Hi... Thanks Bob P....
I experimented with other suggestions, but yours was the one which gave
accurate results...
I can't say I know exactly why.... I don't know how the "Row(Indirect..."
portion of the formula at the end works...

Much obliged,
Rachel



"Bob Phillips" wrote:

=SUM(--(FREQUENCY(IF((B1:B6=0)*(C1:C6=3),MATCH(A1:A6,A1:A 6,0)),ROW(INDIRECT(
"1:"&ROWS(A1:A6))))0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rachel" wrote in message
...
Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result
here.

Thanks for any help...

Counting unique entries with criteria
 

What would the formula look like if you had a third criteria?

"Bob Phillips" wrote:

=SUM(--(FREQUENCY(IF((B1:B6=0)*(C1:C6=3),MATCH(A1:A6,A1:A 6,0)),ROW(INDIRECT(
"1:"&ROWS(A1:A6))))0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rachel" wrote in message
...
Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result
here.

Thanks for any help...

Counting unique entries with criteria
 

That formula has 2 criteria:

(B1:B6=0)*(C1:C6=3)

To add another criteria just follow the same pattern:

(B1:B6=0)*(C1:C6=3)*(criteria 3)

--
Biff
Microsoft Excel MVP


"Lindsey" wrote in message
...
What would the formula look like if you had a third criteria?

"Bob Phillips" wrote:

=SUM(--(FREQUENCY(IF((B1:B6=0)*(C1:C6=3),MATCH(A1:A6,A1:A 6,0)),ROW(INDIRECT(
"1:"&ROWS(A1:A6))))0))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rachel" wrote in message
...
Hi,
I would like to count the number of unique entries in a column which
meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result
here.

Thanks for any help...

Counting unique entries with criteria
 

Bob - you are a genius. Thank you so much. Your formula also worked for me.
--
Jorge.R


"Bob Phillips" wrote:

=SUM(--(FREQUENCY(IF((B1:B6=0)*(C1:C6=3),MATCH(A1:A6,A1:A 6,0)),ROW(INDIRECT(
"1:"&ROWS(A1:A6))))0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rachel" wrote in message
...
Hi,
I would like to count the number of unique entries in a column which meet
various criteria.
A B C
dog 0 3
dogs 0 4
cat 0 3
mice 1 4
cat 0 3
mouse 1 3


So, I would like all unique entries in column A, for which a "0" is
specified in col B and a "3" in col C. I am looking for "2" as a result
here.

Thanks for any help...