Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |