Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if, excluding zeros
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average percentage excluding zeros spanning numerous tabs | Excel Discussion (Misc queries) | |||
Averages excluding zeros | Excel Discussion (Misc queries) | |||
Most frequent item in a list (excluding zeros) | Excel Discussion (Misc queries) | |||
Average, Excluding Zeros, Non-Consecutive Range | Excel Discussion (Misc queries) | |||
EXcluding Zeros from the average in a row | Excel Discussion (Misc queries) |