Select those customers that only has product A or B

Question

I have a list with column Customer and Product and want to create a new column Select where I want to select with 1 for those customers that have A or B, else 0. The expected output:

For example, I do not want to select Customer 3 because he has A, B and C. I only want those customers with A or B, or A and B.

I have tried this =SUM(1/(COUNTIFS($A$2:$A$10;A2;$B$2:$B$10;"A")+COUNTIFS($A$2:$A$10;A2;$B$2:$B$10;"B"))), but it didn't work, and only gave weird numbers or errors.

Answer 1

I was thinking

=--AND(OR(COUNTIFS(A$2:A$10,A2,B$2:B$10,"A"),COUNTIFS(A$2:A$10,A2,B$2:B$10,"B")),NOT(COUNTIFS(A$2:A$10,A2,B$2:B$10,"<>A",B$2:B$10,"<>B")))

but this seems a bit long!

@JvDV and @Justyna MK are absolutely right logically, you could miss out the test for A or B because if it isn't A or B, it must be something else and this would disqualify it, so you can just test for Not A and Not B. In my version it would be

=--NOT(COUNTIFS(A$2:A$10,A2,B$2:B$10,"<>A",B$2:B$10,"<>B"))

The only difference would be if you had a blank for the product, where my original formula would not count it but the reduced formula would:

Answer 2

Here is another option, counting the times customer ordered product A OR B wrapped in a SUM(COUNTIFS(ColA,Customer,ColB,Product A OR B)) fashion, against the amount of rows of that particular customer in the database.

Formula in C2:

=N(SUM(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,{"A","B"}))=COUNTIF($A$2:$A$10,A2))

An even more basic formula would be:

=N(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"<>A",$B$2:$B$10,"<>B")=0)

Where we just test if there are any product bought by the customer that are neither product A nor product B.

Drag down.

Answer 3

Interesting question. Try this formula:

=--NOT(SUMPRODUCT(--($A$2:$A$10=A2),--($B$2:$B$10<>"A"),--($B$2:$B$10<>"B")))

Result: