Hi, I have a pivot table with my source data being salespersonID, SalesDate and ProductID as the rows and count ProductID as the value. I have added another field to the source data with following formula =IF(COUNTIFS($A$2:A2,A2)=1,1,0) showing the distinct SalespersonID and added this field to the Pivot table as value as SUM of...I want to find the number of distinct salespersonID in a certain date range, however I am not getting the right figures. Sorry I am having trouble attaching the example...
counting distinct IDs within a certain date range in a pivot table
- Thread starter suell
- Start date
NARAYANK991
Excel Ninja
Hi ,
Are you using the Pivot Table for other purposes ? If not , the count that you are looking for can be obtained using formulae.
Narayan
Are you using the Pivot Table for other purposes ? If not , the count that you are looking for can be obtained using formulae.
Narayan
NARAYANK991
Excel Ninja
Hi ,
I never say anything is impossible , since my knowledge is very limited.
I can say something is possible either if I have done it in the past , or if I can do it now and upload the file for you. I have not done this in the past , and doing it now is going to be time-consuming. When I put the following :
excel pivot table count unique criteria
in Google , there are several results ; whether they apply to your file , only you can say.
If you can upload your file , which will save me , and others , the time needed to create data and the pivot table , it would help.
Narayan
I never say anything is impossible , since my knowledge is very limited.
I can say something is possible either if I have done it in the past , or if I can do it now and upload the file for you. I have not done this in the past , and doing it now is going to be time-consuming. When I put the following :
excel pivot table count unique criteria
in Google , there are several results ; whether they apply to your file , only you can say.
If you can upload your file , which will save me , and others , the time needed to create data and the pivot table , it would help.
Narayan
This is my data source. I have to use a pivot table to show the number of unique SalespersonID who sold products aaa1, bbb1,ccc1, ddd1 and eee1 during the period 01/01/2013 to 31/01/2013. The answer here should be six SalespersonID.
[pre]
[/pre]
[pre]
Code:
SalespersonID SalesDate ProductID DistinctSalesPersonID
111 1/01/2013 aaa1 1
111 1/01/2013 aaa2 0
111 8/01/2013 aaa1 0
111 10/01/2013 eee1 0
222 1/02/2013 aaa2 1
222 1/02/2013 aaa1 0
222 1/02/2013 aaa3 0
222 2/02/2013 aaa4 0
222 2/02/2013 bbb1 0
333 3/01/2013 aaa3 1
333 12/01/2013 bbb1 0
333 3/04/2013 bbb2 0
444 4/01/2013 aaa4 1
444 9/07/2013 bbb3 0
444 9/07/2013 bbb4 0
555 5/01/2013 bbb1 1
555 3/03/2013 bbb5 0
555 3/03/2013 ccc1 0
555 3/03/2013 ccc2 0
555 3/03/2013 ccc3 0
666 6/01/2013 bbb2 1
666 8/09/2013 ccc4 0
666 8/09/2013 ccc5 0
777 1/08/2013 bbb3 1
777 10/09/2013 ccc6 0
777 10/09/2013 ccc7 0
888 2/03/2013 ccc1 1
999 9/01/2013 ddd1 1NARAYANK991
Excel Ninja
Hi ,
I think there is no need to use the DistinctSalesPersonID column ; use the ProductID in the values , change the Field setting from SUM to COUNT , and change the setting in Show Values As to Index.
Narayan
I think there is no need to use the DistinctSalesPersonID column ; use the ProductID in the values , change the Field setting from SUM to COUNT , and change the setting in Show Values As to Index.
Narayan
NARAYANK991
Excel Ninja
Hi ,
I thought you wanted to know how many distinct salesmen sold any product ; if you select the required products through a filter , the summation of the 1s will give you the number of salesmen ; isn't this what you want ?
Narayan
I thought you wanted to know how many distinct salesmen sold any product ; if you select the required products through a filter , the summation of the 1s will give you the number of salesmen ; isn't this what you want ?
Narayan
NARAYANK991
Excel Ninja
Hi ,
I have not found a way of getting what you want ; let us wait for someone else to pitch in.
I think it is difficult , since what you are looking for is uniqueness after a combination of the product IDs , depending on what you are filtering for.
Thus , if you have only aaa1 as the product ID you want to filter , then the unique count is straightforward , since this can come from the source data itself ; once you filter for more than one product ID , then 2 + 2 is not equal to 4 , since though product ID aaa1 might have two unique salesmen , and product ID bbb1 might have two distinct salesmen , one of them might be common to both aaa1 and bbb1 ; this cannot come from the source data ; it has to come from the pivot table itself , either through a calculated field , or through an formula external to the pivot table.
One of the links in the Google search talks of using an Intermediate Pivot Table ; you can try working it out.
Narayan
I have not found a way of getting what you want ; let us wait for someone else to pitch in.
I think it is difficult , since what you are looking for is uniqueness after a combination of the product IDs , depending on what you are filtering for.
Thus , if you have only aaa1 as the product ID you want to filter , then the unique count is straightforward , since this can come from the source data itself ; once you filter for more than one product ID , then 2 + 2 is not equal to 4 , since though product ID aaa1 might have two unique salesmen , and product ID bbb1 might have two distinct salesmen , one of them might be common to both aaa1 and bbb1 ; this cannot come from the source data ; it has to come from the pivot table itself , either through a calculated field , or through an formula external to the pivot table.
One of the links in the Google search talks of using an Intermediate Pivot Table ; you can try working it out.
Narayan
koi
Member
hi,
what version of excel are you using? if you're using 2013. go for this step
1.add the data into pivot table, but before click ok, please make sure you click "Add to Data Model" then click OK
2. put everything in ROWS, and in Value you put Count of Distinct
3. now right click on count of distinct then choose Value field setting...scroll to the bottom and choose distinct
4. it will appear as 6, although you need to remove the grand total.
what version of excel are you using? if you're using 2013. go for this step
1.add the data into pivot table, but before click ok, please make sure you click "Add to Data Model" then click OK
2. put everything in ROWS, and in Value you put Count of Distinct
3. now right click on count of distinct then choose Value field setting...scroll to the bottom and choose distinct
4. it will appear as 6, although you need to remove the grand total.