Count if, excluding zeros

[brett.kaplan]

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

 

[Bob Phillips]

=SUM(--(FREQUENCY(IF(B1:B20<>0,MATCH(A1:A20,A1:A20,0)),ROW(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)

 

[brett.kaplan]

That works - thanks!!

By the way, what does the "--" before the frequency mean?

Thanks!

 

[Bob Phillips]

It is used to coerce TRUE/FALSE values to 1/0. The FREQUENCY(..)>0 will
return an array of TRUE and FALSE values, the first - coerces this to an
array of -1/0, the second negates it to an array of 1/0, and SUM then just
adds them up.

--
---
HTH

Bob


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