Count with multiple criterias

[Lene S]

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

 

[Mike H]

Perhaps

=SUMPRODUCT((A2:A6=40)*(B2:B6=1))

Mike

 

[Lene S]

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:

 

[Carim]

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

 

[Bob Phillips]

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<>"",CODE(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)

 

[Mike H]

Maybe this

=SUMPRODUCT((A2:A7=40)*(B2:B7=1)*(C2:C7="A"))

Mike

 

[Lene S]

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:

 

[vezerid]

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/D27))

HTH
Kostis Vezerides

 

[Teethless mama]

Try this:

=SUM(N(FREQUENCY(IF((Week=40)*(Machine=1),MATCH(Order,Order,0)),MATCH(Order,Order,0))>0))

ctrl+shift+enter, not just enter