Count with multiple criterias
Hi
I need Excel to count the unique appearances based upon two criterias. The columns needed is: Week Machine Order 40 1 A 40 1 B 40 1 A 40 2 C 41 1 D 41 1 A How do I count the unique numbers of orders in week 40 and for machine 1? In this case 2. Thanks in advance Lene S |
Count with multiple criterias
Perhaps
=SUMPRODUCT((A2:A6=40)*(B2:B6=1)) Mike "Lene S" wrote: Hi I need Excel to count the unique appearances based upon two criterias. The columns needed is: Week Machine Order 40 1 A 40 1 B 40 1 A 40 2 C 41 1 D 41 1 A How do I count the unique numbers of orders in week 40 and for machine 1? In this case 2. Thanks in advance Lene S |
Count with multiple criterias
Hi Mike
The formula returns 3 and this is not the result I am looking for. Machine 1 have spent time on 3 orders in week 40 but order A appears twice. I need the formula to count the number of different orders = 2 in this case. -- Regards Lene S "Mike H" skrev: Perhaps =SUMPRODUCT((A2:A6=40)*(B2:B6=1)) Mike "Lene S" wrote: Hi I need Excel to count the unique appearances based upon two criterias. The columns needed is: Week Machine Order 40 1 A 40 1 B 40 1 A 40 2 C 41 1 D 41 1 A How do I count the unique numbers of orders in week 40 and for machine 1? In this case 2. Thanks in advance Lene S |
Count with multiple criterias
Hi,
Mike is right ... But you seem to have three and not two criteria : =SUMPRODUCT((A2:A6=40)*(B2:B6=1)*(C2:C6="A") HTH Carim |
Count with multiple criterias
If Order is really single characters, then
=COUNT(1/FREQUENCY(IF(A2:A7=40,IF(B2:B7=1,IF(C2:C7<"",CODE (C2:C7)))),IF(A2:A7=40,IF(B2:B7=1,IF(C2:C7<"",COD E(C2:C7)))))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lene S" wrote in message ... Hi I need Excel to count the unique appearances based upon two criterias. The columns needed is: Week Machine Order 40 1 A 40 1 B 40 1 A 40 2 C 41 1 D 41 1 A How do I count the unique numbers of orders in week 40 and for machine 1? In this case 2. Thanks in advance Lene S |
Count with multiple criterias
Maybe this
=SUMPRODUCT((A2:A7=40)*(B2:B7=1)*(C2:C7="A")) Mike "Lene S" wrote: Hi Mike The formula returns 3 and this is not the result I am looking for. Machine 1 have spent time on 3 orders in week 40 but order A appears twice. I need the formula to count the number of different orders = 2 in this case. -- Regards Lene S "Mike H" skrev: Perhaps =SUMPRODUCT((A2:A6=40)*(B2:B6=1)) Mike "Lene S" wrote: Hi I need Excel to count the unique appearances based upon two criterias. The columns needed is: Week Machine Order 40 1 A 40 1 B 40 1 A 40 2 C 41 1 D 41 1 A How do I count the unique numbers of orders in week 40 and for machine 1? In this case 2. Thanks in advance Lene S |
Count with multiple criterias
Maybe I didn't made it clear, sorry.
In real life I do not know the order number, why I cannot use your formula. And actually I do not care whether it is order A, B or something else. I just want to count how many different orders numbers based upon the criterias as to week number and machine number. -- Regards Lene S "Carim" skrev: Hi, Mike is right ... But you seem to have three and not two criteria : =SUMPRODUCT((A2:A6=40)*(B2:B6=1)*(C2:C6="A") HTH Carim |
Count with multiple criterias
Go to D2 and enter the following formula:
=SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C $7=C2)) Now you have the auxiliary cells showing how many times each combination appears. Assume now that week number is in G2 and machine is in H2 . =SUMPRODUCT((A2:A7=G2)*(B2:B7=H2)*(1/D2:D7)) HTH Kostis Vezerides On Dec 10, 2:21 pm, Lene S wrote: Hi I need Excel to count the unique appearances based upon two criterias. The columns needed is: Week Machine Order 40 1 A 40 1 B 40 1 A 40 2 C 41 1 D 41 1 A How do I count the unique numbers of orders in week 40 and for machine 1? In this case 2. Thanks in advance Lene S |
Count with multiple criterias
Try this:
=SUM(N(FREQUENCY(IF((Week=40)*(Machine=1),MATCH(Or der,Order,0)),MATCH(Order,Order,0))0)) ctrl+shift+enter, not just enter "Lene S" wrote: Hi I need Excel to count the unique appearances based upon two criterias. The columns needed is: Week Machine Order 40 1 A 40 1 B 40 1 A 40 2 C 41 1 D 41 1 A How do I count the unique numbers of orders in week 40 and for machine 1? In this case 2. Thanks in advance Lene S |
All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com