SOLVED

Count of Date in Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-2402315%22%20slang%3D%22en-US%22%3ECount%20of%20Date%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2402315%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI%20would%20like%20to%20COUNT%20the%20number%20of%20days%20worked%20within%20this%20pivot%20table%2C%20but%20it%20is%20counting%20every%20line%20entry%20date.%3C%2FP%3E%3CP%3EFor%20example%20this%20Job%20number%20is%20showing%2012%20days%20where%20it%20should%20be%20showing%20only%205%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mel_G_01_1-1622505488641.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285192i45632E482B2CB385%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Mel_G_01_1-1622505488641.png%22%20alt%3D%22Mel_G_01_1-1622505488641.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mel_G_01_0-1622505406447.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285191i48BBA1FBCD91426C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Mel_G_01_0-1622505406447.png%22%20alt%3D%22Mel_G_01_0-1622505406447.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2402315%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2402404%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Date%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2402404%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20count%20the%20number%20of%20UNIQUE%20days%20in%20the%20range%20use%3A%3CBR%20%2F%3E%3DSUMPRODUCT(1%2FCOUNTIF(enterRangeHere%2CenterRangeHere))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2402686%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20of%20Date%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2402686%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20not%20sure%20how%20to%20get%20what%20you%20want%20from%20a%20PivotTable.%3C%2FP%3E%3CP%3EHowever%2C%20Excel%20has%20made%20several%20improvements%20over%20the%20past%20few%20years%20so%20you%20can%20do%20the%20needful%20on%20your%20own%20(assuming%20you%20have%20the%20appropriate%20version%20of%20Excel).%3C%2FP%3E%3CP%3ESuppose%20that%3C%2FP%3E%3CP%3E*%20your%20data%20are%20in%20a%20table%20as%20Excel%20understands%20it%20(Insert%20%7C%20Table)%20and%3C%2FP%3E%3CP%3E*%20that%20the%20job%20of%20interest%20is%20in%20H2.%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20the%201st%20formula%20will%20give%20you%20a%20list%20of%20unique%20dates%20for%20the%20job%20and%20the%202nd%20formula%20will%20give%20you%20a%20count%20of%20the%20unique%20dates.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DUNIQUE(FILTER(Table1%5BDate%5D%2CTable1%5BJob%20No%5D%3DH2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTA(UNIQUE(FILTER(Table1%5BDate%5D%2CTable1%5BJob%20No%5D%3DH2)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067880%22%20target%3D%22_blank%22%3E%40Mel_G_01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

I would like to COUNT the number of days worked within this pivot table, but it is counting every line entry date.

For example this Job number is showing 12 days where it should be showing only 5

Mel_G_01_1-1622505488641.png

Mel_G_01_0-1622505406447.png

 

4 Replies
Hi,

To count the number of UNIQUE days in the range use:
=SUMPRODUCT(1/COUNTIF(enterRangeHere,enterRangeHere))

I'm not sure how to get what you want from a PivotTable.

However, Excel has made several improvements over the past few years so you can do the needful on your own (assuming you have the appropriate version of Excel).

Suppose that

* your data are in a table as Excel understands it (Insert | Table) and

* that the job of interest is in H2. 

Then the 1st formula will give you a list of unique dates for the job and the 2nd formula will give you a count of the unique dates.

=UNIQUE(FILTER(Table1[Date],Table1[Job No]=H2))

 

=COUNTA(UNIQUE(FILTER(Table1[Date],Table1[Job No]=H2)))

 

@Mel_G_01 

Thank you for your suggestions.

I have managed to get the information by including a formula into the data spreadsheet which counted only when the date changed, then refreshed the pivot table so that it showed the actual number of days worked.

best response confirmed by Mel_G_01 (New Contributor)
Solution

@Mel_G_01 

Creating PivotTable add data to data model

image.png

and next in Value Field Setting select Distinct Count

image.png

Without data model this option is not available.