Counting unique values

[giantwolf]

Hi,

I am trying to get a formula whereby I am looking in two columns and
counting unique values in one column that occur when matched against a
specified criteria from another column. ie.

Me Dog
Me Dog
Me Cat
You Dog

Therefore in this example if I wanted to count how many unique values
there were in column a that corresponded with the word "dog" the
formula would return 2 (counting once for the two occurences of "me
dog" and once for the "you dog".

Many thanks in advance,

GW

 

[Guest]

hi, the pivot table will be the best way to solve your problem

hth
regards from Brazil
Marcelo

"giantwolf" escreveu:

 

[giantwolf]

Thanks, I was thinking about a pivot table but I was hoping to have
formula which is already in a table and automatically updates when ne
data is pasted into a different sheet

 

[Guest]

If Columns are like

(A1)Count (B1) Name
Me Dog
Me Dog
You Dog
type in A2
=if(countif(B$1:B2, B2)=1, Max(A$1:A1)+1, "")

Try this it will work

Hitesh

 

[Ron Rosenfeld]

Hi,

I am trying to get a formula whereby I am looking in two columns and
counting unique values in one column that occur when matched against a
specified criteria from another column. ie.

Me Dog
Me Dog
Me Cat
You Dog

Therefore in this example if I wanted to count how many unique values
there were in column a that corresponded with the word "dog" the
formula would return 2 (counting once for the two occurences of "me
dog" and once for the "you dog".

Many thanks in advance,

GW

Here's one way.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this **array-entered** formula:

=COUNTDIFF(IF(B1:B4="Dog",A1:A4),,FALSE)

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. Excel
will place braces {...} around the formula.

"Dog" can be replaced by a cell reference for flexibility.

Another option is this somewhat longer formula, also **array-entered**:

=SUM(IF(FREQUENCY(IF(LEN(IF(B1:B4="Dog",A1:A4,""))>0,
MATCH(IF(B1:B4="Dog",A1:A4,""),IF(B1:B4="Dog",A1:A4,""),0),""),
IF(LEN(IF(B1:B4="Dog",A1:A4,""))>0,MATCH(IF(
B1:B4="Dog",A1:A4,""),IF(B1:B4="Dog",A1:A4,""),0),""))>0,1))


--ron