Search code examples
excelexcel-formulaexcel-pivot

Display Company if Company Sells Two Specific Products

I want to know which companies sell apples and oranges. My data set is as follows

Name   Product

CompA  Apple
CompB  Banana
CompA  Orange
CompB  Apple
CompA  Taco

And I'd like the resulting pivot table (or really any reusable formula) to list something like

Name

CompA

Doesn't really matter as long as I get the list of names. I tried doing a fancy pivot table and adding calculated fields, but they never worked.

Tried things like

=IF(Product="Apple",1,0) // Has Apple
=IF(Product="Orange",1,0) // Has Orange
=IF(AND('Has Apple' >= 1, 'Has Orange' >= 1),Name,"") // Has Apple and Orange

But no luck. The first two formulas never even worked.

My real data set is obviously different and much larger which is why I'm trying to do it through fancy formulas/pivot tables.

Solution

  • Convert to Excel table then add another column and insert the following formula in C2

    =AND(COUNTIFS([Name],[@Name],[Product],"Apple")>0,COUNTIFS([Name],[@Name],[Product],"Orange")>0)

    It should autofill down. Create the pivottable and put the Formula field in the page field area and filter on True