Thanks.

"N Harkawat" wrote:

Since its an array formula the countif function counts the range A4:a65
using the criteria a4, then a5, then A6...all the way to A65 and returns the
values like such
{1,1,2,3,4,3,4,.......2} 62 such values.
Then 1 divided by each of these 62 values and later summed we get
{1,1,1/2,1/3,1/4.....1/2} which when summed returns the result.

"Watercolor artist" wrote in
message ...
I have one more question. In this portion of the formula,
COUNTIF(A4:A65,A4:A65), I know that the first "A4:A65" is the range to
check
and the second parameter, again "A4:A65" is the criteria: That's what
confuses me. How is the range A4:A65 functioning as criteria.

Thanks for your help so far,
Howard

"N Harkawat" wrote:

Its first counts all occurrence of each cell and Then it divides all
these
numbers by 1
Say if you have 3 occurrence of number 7 . First 1 divided by each count
of
the number and then summed so we get 1/3 +1/3+1/3 = 1
Since you had additional restiction of the word "cancelled" before
summing
the above I multiplied all of these by 0 or 1 depending whether the word
"cancelled" existed in Col C
This is achieved from C2:C8 <"cancelled" part of the formula that
returns a
1 (for true the word cancelled exists) and 0 (for false)
Hope it makes sense.

"Watercolor artist" wrote in
message ...
It works. Thanks. Can you explain the underlying logic?

"N Harkawat" wrote:

=SUM(1/COUNTIF(A2:A8,A2:A8)*(C2:C8<"Cancelled"))
array entered (ctrl+shift+enter)

Provided if an order # has cancelled on column C it has it in every
such
same order #
Meaning in your example Order # 12 appear twice and both have
cancelled
and
NOT just once.

"Watercolor artist" wrote
in
message ...
I need a formula that will do the following:

Column A contains order numbers. I want to count all the orders,
however,
if
an order number appears more than once, I only want it counted once.
In
addition, if an order was cancelled, the word "cancelled" would be
in
Column
C for the cancelled order. I don't want any cancelled orders
included
in
the
count.

Col. A Col. C
1
1
7 Cancelled
9
12 Cancelled
12 Cancelled
14

In this situation, the sum of the order numbers would be 3.

Thanks in advance,
Howard