=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
|