+ Reply to Thread
Results 1 to 2 of 2

pivot table with filters

  1. #1
    Registered User
    Join Date
    10-09-2019
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    1

    pivot table with filters

    Hi Folks,

    Sorry for this basic one, I am a newbie to pivot table.

    I am running an excel report with various column and row labels as below. What I am trying is to create a pivot table to filter those names who are using ONLY these products "apple" and "lemon", however not anyone who are using "orange" and "grape".

    For example: jason is using apple, however he is also using orange, so he needs to be excluded.

    So expected result is: tim, tom, adam since all of them are using only those products, and not anything from the exclusion list. I tried with pivot but returning a result with mix of names, what should be right flow of filters and row labels to use here?

    name product
    ------ --------

    jason apple
    jason orange
    tim lemon
    lucy grape
    tom apple
    adam apple
    jen lemon
    jen grape
    luke lemon
    luke orange
    luke apple

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: pivot table with filters

    you cannot do it in the Pivot itself unless you are using PowerPivot. try adding this column in the source. either:
    =SUM(COUNTIFS(A:A,A2,B:B,{"apple","lemon"}))=COUNTIF(A:A,A2)

    or:
    =SUM(COUNTIFS(A:A,A2,B:B,{"orange","grape"}))=0

    add that field to the FILTERS area. filter TRUE. add name to ROWS area

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to select multiple pivot table filters, based on values in a table
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2018, 06:42 PM
  2. Macro to update pivot table filters from multiple pivot tables of different data sources
    By groblerdn85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2017, 05:10 AM
  3. [SOLVED] Pivot Table Filters
    By reedersketer in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 10-12-2017, 08:41 AM
  4. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  5. [SOLVED] What filters to use in pivot table
    By glenath in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-24-2013, 01:04 AM
  6. [SOLVED] Pivot Table Filters
    By gsmcconville in forum Excel General
    Replies: 0
    Last Post: 04-13-2012, 05:31 AM
  7. VBA for Pivot Table Filters
    By Coldsteel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2010, 09:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1