Match index issue?

Hello Excel experts,

I need some help with following problem that I have been struggling with for days. I am trying to count how many orders have Part numbers A and B on the same order. If order 123 has part number A and B on it then return true or else false. I think Match and array might be a way to go but I am still not able to come up with the result that I need. Can you guys help????????????


Here's how the Data looks like:

Order Part numbers
123 A
123 B
123 C
123 D
234 A
234 B
234 E
346 A
346 C

Answer for above would be 2 orders that have A and B part numbers on the same order.

Thanks in advance,

MP

Not very elegant... but perhaps:

=SUMPRODUCT(--(B2:B10="A"),--(ISNUMBER(MATCH(A2:A10&":B",A2:A10&":"&B2:B10,0))))

(flawed if you have multiple instances of Order & Part A)

Are you able to use a column of helper cells ?

Well for ex... if you could use another column adjacent to your data such that (based on example)

C2: =A2&":"&B2
copied down to C10

Then you could use the below for your total:

=SUMPRODUCT(--(B2:B10="A"),--(ISNUMBER(MATCH(A2:A10&":B",C2:C10,0))),1/COUNTIF(C2:C10,C2:C10&""))

This would account for multiple instances of Order:A

even uglier is this youwill get at least one true for each id if both conditions are matched part numbers to match go in d1 and e1