COUNTIF Problem

[DF]

Hi all .. please help.

ColA ColB ColC ColD

P1 200 P1 3
P1 201 P2 2
P1 202 P3 1
P2 203
P2 204
P1 200
P3 205
P3 205
P1 200

ColA & ColB & ColC are predefined. How do I compose the formula to
obtain the unique count in ColD? I have am totally stuck here. Any
assistance is greatly appreciated.

Thanks,
Dave

 

[Norman Harker]

Hi DF!

See:

Chip Pearson:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

All manner of formulas for handling unique / duplicate problems.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

 

[Vasant Nanavati]

Perhaps:

=SUM((1/COUNTIF($B$1:$B$9,$B$1:$B$9))*($C1=$A$1:$A$9))

entered as an array formula with <Ctrl> <Shift> <Enter>. Copy down as
needed.

Not quite sure if Chip's page addresses this particular wrinkle.

 

[Aladin Akyurek]

If you download & install the morefunc.xll add-in
(http://longre.free.fr/english), you can invoke:

=COUNTDIFF(SETV(IF($A$1:$A$9=C1,$B$1:$B$9)))-ISNUMBER(MATCH(FALSE,GETV(),0))

which must be confirmed with control+shift+enter instead of just with enter.