help with duplicates and IF formulas

[algl05ab]

Hi,

I hope you can help me.

I have a sheet of several thousand rows. Here I have OrderIDs on sales. What I need is this:

if OrderID has only Product 1 then output should be 6. if OrderID has Product 1 and 2 then output should be 1. I will need the yellow cells to be filled out.

The orderIDs migt not neccesarily be together in the file. Meaning a OrderID A that has both product 1 and 2 might have one row for product 1 in row 10 and product 2 row will be row 100000.

I attached a small file which I hope you can help with.

thanks!
br, Alexander

[sandy666]

something like this?
=IF(COUNTIF($D$2:$D$17,D2)>1,6,1)

or to check if in B column doesn't exist any other value but 1 or 2 only
=IF(OR(B2=1,B2=2),IF(COUNTIF($D$2:$D$17,D2)>1,6,1))

[algl05ab]

perhaps. If there are more than one Order ID 32100087708125 then it says 6. However, there will be cases where sometimes there will be more than 1 of the same order ID but with the same productgroup, e.g. A. Then it should say 1.

IF(COUNTIF($D$2:$D$17,D2)>1,6,1)

[sandy666]

so
if
B2=1,C2=A,D2=single result=?
B2=1,C2=B,D2=single result=?
B2=1,C2=A,D2=dup result=?
B2=1,C2=B,D2=dup result=?
the same for B2=2 etc

attach example with all possibilities

[algl05ab]

it's tricky to explain.

Imagine sales where one can buy a mobil subscription and an iPhone. If your orderID has 2 duplicated this may indicate one row for the subscription and one for the handset. however, in some cases there might be sold 2 subscriptions and no iPhones in the same orderID.
if there is only one row its a subscribtion only. See attached.

thanks!

[sandy666]

I asked for example with atypical cases. second example is the same as first, except you changed 1 and 2 to handset and subs and deleted A,B. so this formula works (with second example also)
=IF(COUNTIF($C$2:$C$17,C2)>1,6,1)