help with a countif

[Jinjunwei]

hi,

I have a sheet that looks like this:

Col A Col B
ID # Provider
292890322 JBs
292890322 Smiths
292890326 JBs
292890326 Wilbright
292890326 Mixie
292890326 Equity Part
292890343 JBs
292890343 Equity Part
292890342 JBs
292890342 Smiths




Col A represents produts, and Col B represent's clients (customers). So, Product 292890322 was sold to both JBs and Smiths. Product 292890326 was sold to Smiths and Wilbright and so on.

I wanted to figure out which customers are competing with JBs. So above, JB bought 4 products. 50% of the time (or twice), JBs was competing with Smiths. 25% of the time, JBs was competing with Equity Part, and 25% of the time Wilbright.

I think I want a countif, but I am all confused. It has been a long day. thank you so much for any help.

 

[Jinjunwei]

I was trying to use a pivit table to do this... but I couldn't figure out how to to get the same fields in both a column and row (ie, see where the counts overlap which is what I want to see... product x purchased by customer 1 and 2)