Counting Unique Cells based on multiple criteria Or a Pivot Table

Question

Counting Unique Cells based on multiple criteria Or a Pivot Table

Anonymous

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

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

Answer accepted by question author

9 additional answers

Answer 1

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.

Answer 2

Anonymous

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.

0 comments

Answer 3

Anonymous

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

Joe

0 comments

Answer 4

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 **** 15379357 901-031-637-111 Existing

Manager 1 Buyer 4 **** 15281954 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 **** 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 **** 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 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 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 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

Answer 5

Anonymous

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

0 comments

Share via

Counting Unique Cells based on multiple criteria Or a Pivot Table

9 additional answers