Array formula help

[David]

Gretings and tia for your help

I have a database like this:
Name Code
a 1
b 0
b 6
c 5
d 6
c 5
c 0

In a seperate table, with each name appearing once, I want to count how many code variants appear next to each name, the result would look like this:

Name Code variants
a 1
b 1
c 2
d 1

Can anyone suggest an array formula to count the code variants?

 

[Frank Kabel]

Hi
for conditional counting unique entries try the following
array formula (entered with CTRL+SHIFT+ENTER):
=SUM(IF(FREQUENCY(IF(('sheet1'!$A$2:$A$100=$A2),MATCH
('sheet1'!$B$2:$B$100,'sheet1'!$B$2:$B$100,0),""),IF
(('sheet1'!$A$2:$A$100=$A2),MATCH
('sheet1'!$B$2:$B$100,'sheet1'!$B$2:$B$100,0),""))>0,1))
and copy this down

assumptions:
- sheet1 is your data sheet
- on your second sheet column A contains the lookup values
for column a on the first sheet
- col. B on sheet 1 contains the data
- data start in row 2

-----Original Message-----
Gretings and tia for your help

I have a database like this:
Name Code
a 1
b 0
b 6
c 5
d 6
c 5
c 0

In a seperate table, with each name appearing once, I

want to count how many code variants appear next to each
name, the result would look like this:

 

[Jerry W. Lewis]

What do you mean by code variants? "b" has both 0 and 6 codes, but your
desired table says that "b" has only 1 code variant.

If you mean how many entries with a nonzero code, regarless of whether
they duplicate previous code entries for that name, then use
=SUMPRODUCT((name_range="b")*(code_range<>0))
will return 1, and the corresponding formula for "c" will return 2.

If you mean how many unique entries (including zero), use the
COUNTDIFF() function in Laurent Longre's MoreFunc.xll, which you can
download from
http://longre.free.fr/english/
The array formula
=COUNTDIFF(IF(name_range="b",code_range,""))-1
will return 2, and the corresponding formula for "c" will also return 2.

Jerry

Gretings and tia for your help

I have a database like this:
Name Code
a 1
b 0
b 6
c 5
d 6
c 5
c 0

In a seperate table, with each name appearing once, I want to count how

 

[David]

Thanks Frank

That worked like a dream

 

[David]

Jerry,
Yes, my mistake, I was looking for unique entries
Thanks for your help

 

[Jerry W. Lewis]

You're welcome

Jerry