Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Watercolor artist
 
Posts: n/a
Default 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
  #2   Report Post  
N Harkawat
 
Posts: n/a
Default

=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



  #3   Report Post  
Watercolor artist
 
Posts: n/a
Default

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




  #4   Report Post  
N Harkawat
 
Posts: n/a
Default

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






  #5   Report Post  
Watercolor artist
 
Posts: n/a
Default

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








  #6   Report Post  
N Harkawat
 
Posts: n/a
Default

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








  #7   Report Post  
Watercolor artist
 
Posts: n/a
Default

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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
put formula results into a different cell if it is empty PutFormula Excel Worksheet Functions 2 February 11th 05 04:31 AM
how do i write a formula and keep in in formula form, so it DOESN. norcalchick2207 Excel Discussion (Misc queries) 2 February 4th 05 09:38 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"