Need a formula that counts orders
 

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

=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

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

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

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

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

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