Counting unique values

  • Thread starter Thread starter giantwolf
  • Start date Start date
G

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
 
hi, the pivot table will be the best way to solve your problem

hth
regards from Brazil
Marcelo

"giantwolf" escreveu:
 
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
 
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
 
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
Click to expand...

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
 
Back
Top