Hey, Domenic


=COUNT(1/FREQUENCY(IF($B$2:$B$5=E2,IF($A$2:$A$5<"",MATCH($ A$2:$A$5,$A$2:$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1))

After looking at THAT formula....I don't know what in world drove me to use
the clunky approach I used!

Nice work.
***********
Best Regards,
Ron

XL2002, WinXP


"Domenic" wrote:

In article .com,
"Hari" wrote:

Are there pure formula based solutions to this problem (without using
pivots). Probably some variation of an array sumproduct along with
"embedded" countif kind of formula?

Assuming that A2:C5 contains the data, let E2 and E3 contain x and y,
respectively, then try the following...

For a unique count, if you download and install the free add-in
Morefunc.xll...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=COUNTDIFF(IF($B$2:$B$5=E2,$A$2:$A$5,0),FALSE,0)

Otherwise...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=COUNT(1/FREQUENCY(IF($B$2:$B$5=E2,IF($A$2:$A$5<"",MATCH($ A$2:$A$5,$A$2:
$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1))

To sum units...

G2, copied down:

=SUMIF($B$2:$B$5,E2,$C$2:$C$5)

Hope this helps!