=SUM(--(FREQUENCY(IF(B1:B20<0,MATCH(A1:A20,A1:A20,0)),RO W(INDIRECT("1:"&ROWS(A1:A20))))0))

which is an array formula, Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
Hi,

What I'm trying to do is, in one cell, get a count of unique values in
a column that, in the adjacent column, do not have a value of 0.

For instance, if my table looked like this:

A 1
A 1
B 1
B 1
B 1
C 0
C 0
D 1

I'd want a formula that would give me the value of 3, such that it
counts how many unique values there are in the first column and then,
if the sum of that unique value is 0, it excludes it from the count.

I could do this in a third column, by putting the formula:
=IF(OR(B3=B2,C3=0),D2,D2+1) (assuming the first "A" is in cell B3),
and then dropping that down and taking the max value, however, I'd
like to do this in one cell.

Is this possible, perhaps using an array? I'm already using {=SUM(1/
COUNTIF($B$3:$B$12,$B$3:$B$12))} to get me the unique value count, but
can't figure out how to exclude the ones with a total of 0.

Thanks in advance!

Brett