Share via

Counting Unique Cells based on multiple criteria Or a Pivot Table

Anonymous
2020-11-04T18:14:51+00:00

First, thank you in advance for your help.

I have a spreadsheet with approximately 63,000 rows and 39 columns. I am trying to count the number of unique part numbers for a buyer when the part was just added (New). I set up a pivot table like below that under the Values I have Count of "Part Number", under Rows is "Buyer" and "Part Number" and under Filters is "Buyer Manager" and "New or Existing". In the example below I am setting the filter to "Buyer Manager" and "New". The results I get are not always what I am looking for, under the Total column it adds the values together and I have the Total field set to Count of Part Number. For instance under Buyer 2 there is only three part numbers but "New" shows up four times, twice for part number 145VS126-1 so I get a Buyer Total of four but I should only have three because I want to count the unique number of part numbers. I looked at the source data and for Buyer 2 there are four occurrences of New but only three part number because one shows up twice which is correct but I only want to count it once. Is there a way to do this either with the Pivot Table or a formula? The Total column is based on "New" parts.

Thank you, Joe

Count of Part Number                  

Buyer                    Part Number                      Total

Buyer 1                 114E5079-21                       1

Buyer 1                Total                                     1

Buyer 2                 114ES285-3                         1

                                145VS126-1                       2

                                145VS126-3                       1

Buyer 2                Total                                     4

Buyer 3                 114E5109-2                         1

                                901-031-525-144              1

Buyer 3                Total                                     2

Buyer 4                 114S2108-915                     1

                                114S3902-373                    1

                                901-031-389-194               1

                                901-031-963-117               1

Buyer 4                Total                                     4

Buyer 5                 145S4906-53M01              1

                                724S3709-227                   1

Buyer 5                Total                                     2

Buyer 6                 74A183102-K904M04        1

                                74A190852-92043             1

                                74A190852-92063             1

Buyer 6                Total                                     3

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. Anonymous
    2020-11-11T14:33:15+00:00

    If you create a Pivot Table and don't click the "Add this data to the data model" can you go back and select it after you have created the pivot table

    Have a look into this thread:

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2013_release/how-do-i-add-a-data-model-to-an-existing-pivot/e18335d0-ece5-432c-8468-dd743abe36ba?tm=1605105130057

    Andreas.

    1 person found this answer helpful.
    0 comments No comments

9 additional answers

Sort by: Most helpful
  1. Anonymous
    2020-11-05T13:36:12+00:00

    Create a Pivot Table and check Add this data to the Data Model:

    Setup the Pivot table as usual, then change the Field Settings of the Part number to Distinct Count

    Sample file:

    https://www.dropbox.com/s/r4hwhp8eejd1myq/0e3946e3-0aca-4858-b488-e70f3eb932bd.xlsx?dl=1

    Andreas.

    1 person found this answer helpful.
    0 comments No comments
  2. Anonymous
    2020-11-05T14:55:28+00:00

    Looks like that worked perfectly Andreas, thank you. I never knew how to get Distinct Count before.

    Joe

    0 comments No comments
  3. Anonymous
    2020-11-05T12:29:46+00:00

    Below is a sample of the data I have. I want to end up with showing the only the New part numbers and a total count for those which would be represented in the first post. Although it should say Buyer 2 Total as 3 not 4 because there are only three part numbers but the word New shows up four times

    When I run my Pivot Table I get the below results. Under the Existing column total for Buyer 1 where it has 5 I would like for it to show 3 because there are 3 existing part numbers, not 5 and under Part Number 4 because there are 4. Under Buyer 2 it should show 2 for Existing, 3 for New and 4 for the Totals. I think the reason the numbers are off is because there are duplicates of some of the part numbers.

    I want to end up with this if possible. Count only the number of times a value shows under each column, not sum the values. I do use count in my Pivot Table.

    Thank you,

    Joe

    Raw data

    Buyer Manager Buyer **** Work Order        Part Number                     New or Existing

    Manager 1          Buyer 4 **** 15281951             901-031-637-111               Existing

    Manager 1          Buyer 4 **** 15281951             901-031-637-111               Existing

    Manager 1          Buyer 4 **** 15281951             901-031-637-111               Existing

    Manager 1          Buyer 4 **** 15281951             901-031-637-111               Existing

    Manager 1          Buyer 4 **** 15281951             901-031-637-111               Existing

    Manager 1          Buyer 4 **** 15379357             901-031-637-111               Existing

    Manager 1          Buyer 4 **** 15379357             901-031-637-111               Existing

    Manager 1          Buyer 4 **** 15281954             901-031-637-112               Existing

    Manager 1          Buyer 4 **** 15281954             901-031-637-112               Existing

    Manager 1          Buyer 4 **** 15281954             901-031-637-112               Existing

    Manager 1          Buyer 4 **** 15281954             901-031-637-112               Existing

    Manager 1          Buyer 4 **** 15281954             901-031-637-112               Existing

    Manager 1          Buyer 4 **** 15379356             901-031-637-112               Existing

    Manager 1          Buyer 4 **** 15379356             901-031-637-112               Existing

    Manager 1          Buyer 6 **** 15237992             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 15237992             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 15237992             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 15237992             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 15237992             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 15364951             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 15364951             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 15364951             901-031-771-111               Existing

    Manager 1          Buyer 6 **** 16083643             74A183102-K904M04      New

    Manager 1          Buyer 2 **** 16072223             125WS160-200                  Existing

    Manager 1          Buyer 2 **** 16072227             125WS160-200                  Existing

    Manager 1          Buyer 2 **** 16072231             125WS160-200                  Existing

    Manager 1          Buyer 2 **** 16072234             125WS160-200                  Existing

    Manager 1          Buyer 1 **** 16057310             145S2973-11                       Existing

    Manager 1          Buyer 1 **** 16065157             901-571-713-103               Existing

    Manager 1          Buyer 1 **** 15977753             234S5534-10                       Existing

    Manager 1          Buyer 1 **** 15977753             234S5534-10                       Existing

    Manager 1          Buyer 1 **** 16080274             234S5534-10                       Existing

    Manager 1          Buyer 2 **** 15378142             114ES285-3                         Existing

    Manager 1          Buyer 2 **** 15383622             114ES285-3                         Existing

    Manager 1          Buyer 2 **** 15582617             114ES285-3                         Existing

    Manager 1          Buyer 2 **** 15692916             114ES285-3                         Existing

    Manager 1          Buyer 2     15692921             114ES285-3                        Existing

    Manager 1          Buyer 2     15692927             114ES285-3                        Existing

    Manager 1          Buyer 2     15692934             114ES285-3                        Existing

    Manager 1          Buyer 2     15933311             114ES285-3                        Existing

    Manager 1          Buyer 2     16083383             114ES285-3                        New

    Manager 1          Buyer 3     15765393             724S8057-203                    Existing

    Manager 1          Buyer 3     15765393             724S8057-203                    Existing

    Manager 1          Buyer 3     15765393             724S8057-203                    Existing

    Manager 1          Buyer 3     15765405             724S8057-203                    Existing

    Manager 1          Buyer 3     15765405             724S8057-203                    Existing

    Manager 1          Buyer 3     15765405             724S8057-203                    Existing

    Manager 1          Buyer 3     15765405             724S8057-203                    Existing

    Manager 1          Buyer 3     15765428             724S8057-203                    Existing

    Manager 1          Buyer 3     15765436             724S8057-203                    Existing

    Manager 1          Buyer 5     15133296             901-031-804-103              Existing

    Manager 1          Buyer 5     15133296             901-031-804-103              Existing

    Manager 1          Buyer 5     15133296             901-031-804-103              Existing

    Manager 1          Buyer 5     15133296             901-031-804-103              Existing

    Manager 1          Buyer 5     15133296             901-031-804-103              Existing

    Manager 1          Buyer 5     15790103             901-031-804-103              Existing

    Manager 1          Buyer 3     15976780             145S5528-26                      Existing

    Manager 1          Buyer 3     15976789             145S5528-26                      Existing

    Manager 1          Buyer 3     15976793             145S5528-26                      Existing

    Manager 1          Buyer 5     15084232             234S8057-17                      Existing

    Manager 1          Buyer 5     15084232             234S8057-17                      Existing

    Manager 1          Buyer 5     15084232             234S8057-17                      Existing

    Manager 1          Buyer 2     16083458             145VS126-1                        New

    Manager 1          Buyer 2     16083459             145VS126-1                        New

    Manager 1          Buyer 2     16083460             145VS126-3                        New

    Manager 1          Buyer 3     16083681             901-031-525-144              New

    Manager 1          Buyer 5     16083637             724S3709-227                    New

    Manager 1          Buyer 4     16083680             901-031-389-194              New

    Manager 1          Buyer 4     16083410             114S2108-915                    New

    Manager 1          Buyer 6     16083646             74A190852-92043            New

    Manager 1          Buyer 6     16083647             74A190852-92063            New

    Manager 1          Buyer 4     16083687             901-031-963-117              New

    Manager 1          Buyer 5     16083456             145S4906-53M01             New

    Manager 1          Buyer 3    16083363             114E5109-2                        New

    Manager 1          Buyer 4     16083412             114S3902-373                    New

    Manager 1          Buyer 1     16083362             114E5079-21                      New

    0 comments No comments
  4. Anonymous
    2020-11-05T01:55:03+00:00

    Your question is not clear.  Share the raw data and show the expected result.

    0 comments No comments