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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if, excluding zeros
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if, excluding zeros
That works - thanks!!
By the way, what does the "--" before the frequency mean? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count if, excluding zeros
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) wrote in message ... That works - thanks!! By the way, what does the "--" before the frequency mean? Thanks! |
Reply |
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) |