Counting unique values
 

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


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=567923

Counting unique values
 

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

hth
regards from Brazil
Marcelo

"giantwolf" escreveu:


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


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=567923

Counting unique values
 

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


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=567923

Counting unique values
 

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

"giantwolf" wrote:


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


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=567923

Counting unique values
 

On Thu, 3 Aug 2006 11:01:25 -0400, giantwolf
wrote:


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:A 4,""),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