countif

  • Thread starter Thread starter not so bright joe
  • Start date Start date
N

not so bright joe

i have a column with 1200 values, some of them repeat. i
want to count the number of different(unique) values.
what is the command to count values within a range without
counting repeat values. thanks
 
Assuming you data starts in cell A2, put the following formula in another cell in row 2:

=IF(COUNTIF($A$2:A2,A2)=1,1,0)

Copy or fill down to the bottom of you data. If the formula is in a column directly to the right of your data you can just double click on the fill handle(black square in lower right corner of selected cell).

This will return a 1 for every unique record and a zero for all duplicates. Assuming the IF functions are in column B, then =SUM(B:B) will give you the total.

Good Luck,
Mark Graesser
(e-mail address removed)

----- not so bright joe wrote: -----

i have a column with 1200 values, some of them repeat. i
want to count the number of different(unique) values.
what is the command to count values within a range without
counting repeat values. thanks
 
hold Ctrl+Shift while entering
=SUM(1/COUNTIF(A1:A1200,A1:A1200)

----- not so bright joe wrote: ----

i have a column with 1200 values, some of them repeat. i
want to count the number of different(unique) values.
what is the command to count values within a range without
counting repeat values. thank
 
Bob Umlas said:
hold Ctrl+Shift while entering:
=SUM(1/COUNTIF(A1:A1200,A1:A1200))
Click to expand...
....

Or use SUMPRODUCT rather than SUM to avoid the need for array entry. Note
that the formula above will return #DIV/0! errors if any of the cells in the
range A1:A1200 are blank. If this is a possibility,

=SUMPRODUCT(1/COUNTIF(A1:A1200,A1:A1200&""))

will count truly blank cells as well as cells evaluating to zero length
strings as another distinct value, while

=SUMPRODUCT((A1:A1200<>"")/COUNTIF(A1:A1200,A1:A1200&""))

will exclude truly blank cells as well as cells evaluating to zero length
strings. Neither of these will return #DIV/0! no matter what's in the range.
 
Back
Top