How to filter out any client with specific products in their account

Lissette Roman 0 Reputation points
2026-10-09T00:32:26.5766667+00:00

I have an Excel spreadsheet with a list of clients and products as well as account representatives assigned for each client. I need to identify all clients whose combination of products include specific products within that combination and identify clients that do not have specified products at all. A pivot table is not allowing this.

Ex. our customer account rep, customer name, customer product A, B, C, D,etc.

Need to filter out clients that show any of product D & E, even if they have product A, B, C.

Microsoft 365 and Office | Excel | For business | Windows
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102.6K Reputation points Volunteer Moderator
    2026-10-09T22:59:26.18+00:00

    Hi,

    Create a Pivot Table and drag Customer to the row labels. Create a slicer of Product and select D and E. Drag customer to the value area section.

    Was this answer helpful?

    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2026-10-09T05:32:57.92+00:00

    Sample file:
    https://www.dropbox.com/scl/fi/654up6x75lihwsa7b6653/6031029.xlsx?rlkey=ylfn0jl1wxjx4zz4uz5wdxjq0&dl=1

    grafik

    E2:

    =SORT(UNIQUE( FILTER(B2:B21,(C2:C21="D")+(C2:C21="E"),"(none)")))

    G2:

    =LET(
    DorE, FILTER(B2:B21, (C2:C21="D") + (C2:C21="E")),
   SORT(UNIQUE(FILTER(B2:B21, ISNA(MATCH(B2:B21, DorE, 0)), "(none)")))
)

    I2:

    =LET(
    D, FILTER(B2:B21, C2:C21="D"),
    E, FILTER(B2:B21, C2:C21="E"),
    SORT(UNIQUE(FILTER(D, ISNUMBER(MATCH(D, E, 0)), "(none)")))
)

    K2:

    =LET(
    D, FILTER(B2:B21, C2:C21="D"),
    E, FILTER(B2:B21, C2:C21="E"),
    D_und_E, FILTER(D, ISNUMBER(MATCH(D, E, 0)), ""),
    SORT(UNIQUE(FILTER(B2:B21, ISNA(MATCH(B2:B21, D_und_E, 0)), "(none)")))
)

    Was this answer helpful?

    0 comments
  3. Teddie Dang 1,460 Reputation points Independent Advisor
    2026-10-09T01:55:18.9566667+00:00

    Hi @Lissette Roman

    The approach will depend on how the data is structured. Here are two examples:

    1.One row per client, with each product in a separate columnUser's image

    To identify clients that have either Product D or Product E:

    =IF(COUNTIF(F2:G2,"Yes")>0,"Exclude","Keep")

    To identify clients that have both Product D and Product E:

    =IF(COUNTIF(F2:G2,"Yes")=2,"Exclude","Keep")

    User's image

    2.One row per product

    User's image

    In F2, create a unique customer list:

    =UNIQUE(B2:B1000)

    Then, use the following formulas according to your requirement.

    -Customers with both Product D and Product E

    =AND(COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"D")>0,COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"E")>0)

    -Customers with Product D or Product E

    =OR(COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"D")>0,COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"E")>0)

    -Customers with neither Product D nor Product E

    =AND(COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"D")=0,COUNTIFS($B$2:$B$1000,F2,$C$2:$C$1000,"E")=0)

    User's image

    I hope this helps.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.