Forum Discussion

Pablo Fernandes's avatar
Pablo Fernandes
Icon for Microsoft rankMicrosoft
Dec 20, 2018

Filter "companies" that have at least 3 specific "products"

I have an excel pivot table (and a table dataset behind) that has the structure like the one below. How can I filter/show only companies (Col A) with Products (Col B) 1 AND 2 AND 3? Sounds like something easy but can't find a way to do that. Thank you in advance for any help.

 

A1: Company 1 | B1: Product 1
A2: Company 1 | B2: Product 2
A3: Company 1 | B3: Product 3
A4: Company 1 | B4: Product 4
A5: Company 2 | B5: Product 1
A6: Company 3 | B6: Product 1
A7: Company 4 | B7: Product 1
A8: Company 4 | B8: Product 2
A9: Company 4 | B9: Product 3
A10: Company 4 | B9: Product 4
A11: Company 4 | B9: Product 5
BI & Data Analysis
excel
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Dec 25, 2018

    The first way I can figure out is with a second table and COUNTIFS formula as in the attachement.

     

    The second table must have ALL the companies (just once) and ALL the products (just once). Obviously you can have the second table in a different sheet/document for graphic purposes.

  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Dec 25, 2018

    The first way I can figure out is with a second table and COUNTIFS formula as in the attachement.

     

    The second table must have ALL the companies (just once) and ALL the products (just once). Obviously you can have the second table in a different sheet/document for graphic purposes.

Resources