if it only different in that we are giving it a multicell contiguous range
of criteria rather than a single cell. This happens to be the same range as
the first argument, so we are counting the number of times each value in
each cell in range is found in the entire range

Look at the first two cell
A1: 1
A2: 2

the first element in the array is the number of times 1 (from A1) occurs in
the range. It occurs once, so the array contains a 1 as the first element.

the second element in the array is the number of times 2 (from A2) occurs in
the range. It occurs 4 times, so the array contains a 4 as the first
element. If 2 occurs 4 times, then there should be 3 other cells that
contain a 2 and in the array at the corresponding location you will find a 4
reflected as well.

You can reproduce the array in cells by going to b1 and entering

=Countif($A$1:$A$12,A1) and drag fill down to column b12

this is the way countif works traditionally (and as shown in help).
Apparently it was discovered that it can accept a contiguous range as the
second argument and return an array. Not all capabilities are covered in
Help. some are discovered using experimentation.

Then again, if the behavior is not written down, there should be no
restriction on Microsoft changing the behavior in a later version.

--
Regards,
Tom Ogilvy

wrote in message
...
In the Countif help it shows Countif (range,criteria), you
mention Countif (range,range) is there a difference here?

Also, I'm not understanding how the array returned by the
Countif is actually generated, what do the numbers in the
array represent?

Thanks for the time spent helping. . .

-----Original Message-----
the formula boils down to

=if(max(Countif(range,range))1,"Duplicates,"No
Duplicates")

Countif(range,range) would return an array like
{1;4;1;1;1;1;1;2;4;4;4;2}

for a column with data like:

1
2
3
4
5
6
7
8
2
2
2
8


Max finds the max number in the array. If it is greater
than 1, then there
are duplicates. Otherwise, there are not.

The other part of the formula is just finding the max row
with a value so
the range dynamically determines the range of cells to
check.

--
Regards,
Tom Ogilvy


"Steve" wrote in message
...
I found the formula below some place and I am trying to
understand the logic behind it. It finds duplicate
entries
in a column. If anyone would like to give me a hand in
understanding it I would appreciate it. I am a beginner
in
the fomula creation world and I think by understanding
other people's logic it will help me as I develope my
own
formulas.

=IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A500<"") *ROW
(A2:A500)))),INDIRECT("A2:A"&(MAX((A2:A500<"")*RO W
(A2:A500))))))1,"Duplicates","No Duplicates")

Thanks


.