I have a question similar to my last. I am trying to figure out how to find customers who have bought only tape, pen and stapler once and nothing else.
Name CustmoerID Item Name Item # Desired Results
Bob 1 Pen 555
Bob 1 Stapler 222
Bob 1 Stapler 222
Bob 1 Tape 111
Greg 2 Pen 555
Greg 2 Pen 555
Greg 2 Stapler 222
Tim 3 Stapler 222
Tim 3 Tape 666
Tim 3 Glue 333
Mark 4 Pen 555 Check
Mark 4 Stapler 222 Check
Mark 4 Tape 111 Check
This is the closet I think I got. But no luck.
=IF(AND(OR(D2={111}),COUNTIF(B:B,B2)=1)*AND(OR(D2={222}),COUNTIF(B:B,B2)=1)*AND(OR(D2={555}),COUNTIF(B:B,B2)=1),"Check","")
Edit- This got me a bit closer but still not working properly.
=IF(AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3),"Check","")
This allows the use of two codes for the same thing and checks if there are three.
=IF(AND(COUNTIF(B:B,B2)=3,SUMPRODUCT(--(COUNTIFS(B:B,B2,D:D,{222,111,777,555})=1))=3),"Check","")