Need a formula that counts orders

[Watercolor artist]

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

[N Harkawat]

=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" <Watercol...@discussions.microsoft.com> wrote in
message news:52AFAB50-0CBD-485B...@microsoft.com...

[Watercolor artist]

It works. Thanks. Can you explain the underlying logic?

[N Harkawat]

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" <Watercol...@discussions.microsoft.com> wrote in

message news:B8B4B834-D002-429F...@microsoft.com...

[Watercolor artist]

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]

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" <Watercol...@discussions.microsoft.com> wrote in

message news:6A23834C-41EE-443C...@microsoft.com...

[Watercolor artist]

Thanks.