Search code examples
excelexcel-formulaexcel-2013

How can I find clients that have bought three of a certain item and only those 3 items with excel formula


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

Solution

  • 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","")
    

    enter image description here