COUNT HELP

[ml]

I HAVE A DATASET LOOKING LIKE THIS...

DEPT NAME
503 BLACKMON, S
648 BROWN, C
640 HOGAN, J
202 HOWARD, C
503 LAWSON, M
402 TAYLOR, T
402 TAYLOR, T


I WANT TO SET UP A FORMULA TO COUNT TOTAL EMPLOYEES WITHIN THE DEPT,
DUPLICATE NAME WITHIN DEPT SHOULD COUNT AS 1. FOR EXAMPLE: THE ANSWER I
SHOULD GET FOR DEPT 402 IS 1, NOT 2. HELP PLEASE!!! BTW - I HAVE EXCEL 2003.

 

[Tim879]

If your data is sorted by DEPT then NAME, you could use the following
formulas

(assumes data below is in Col's A + B)

In cell C2 put =if(a1&b1=a2&b2,0,1)

You can then use the following formula to summarize by dept.
In cell D2 put =sumif($A:$A,<DEPT NUMBER HERE>, $C:$C)
just replace <DEPT NUMBER HERE> with the cell reference of the Dept or
the dept # in quote e.g. "402"

 

[T. Valko]

Try this array formula** :

Assume there are no empty cells in the Name column.

A2:A8 = dept number
B2:B8 = names

D2 = dept number of interest

=COUNT(1/FREQUENCY(IF(A2:A8=D2,MATCH(B2:B8,B2:B8,0)),ROW(B2:B8)-MIN(ROW(B2:B8))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[ml]

Try this array formula** :

Assume there are no empty cells in the Name column.

A2:A8 = dept number
B2:B8 = names

D2 = dept number of interest

=COUNT(1/FREQUENCY(IF(A2:A8=D2,MATCH(B2:B8,B2:B8,0)),ROW(B2:B8)-MIN(ROW(B2:B8))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[ml]

Thank you Tim, but is there another way if I don't want data to be sorted?

 

[ml]

Thanks T. Valko. I tried this solution but they all came up with 0 (zero).
What did I do wrong here?

 

[T. Valko]

Did you enter the formula as an array?

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

Are there any empty cells within the Name range?

 

[ml]

Yes, I entered the formula as an array and no there are no empty cells within
the name range or the dept range.

 

[T. Valko]

Here's a small sample file that demonstrates this:

xCountUniques.xls 14kb

http://cjoint.com/?bwxbXFMo4a