pivot table question

[steve400243]

Hello, I use a pivot table to show how many customer reference numbers are done in a month. I would like the count column to show just the total? Is this possible? I cannot figure out how to make this happen. Thanks for any help provided.

Reference	Count
Magnabilities	1
79907252685	1
Mechatronics	10
79903088794	1
79904096168	1
79906457350	1
79906925828	1
79904818173 / ASQNSH17040483	1
79908520114	1
79904054639 / FTNVSHS000146568	1
79904054407	1
79905323132	1
79904681852	1
Montana Fly	1
79908133272	1
Olympic Foundry	1
79903047972	1
Totals	13

[p45cal]

From the little that you give us, lose the subtotals, perhaps lose the column labels that include Magnabilities etc., and collapse the groups, or perhaps have a pivot with nothing in the row and column label areas and just a count of Reference in the Values area.
Supply a file and we can be more explicit.

[steve400243]

Hello P45cal, Here is the file. Thank you for your help.

[p45cal]

In the attached, in the vicinity of cell S3 of the Summary sheet there's a small pivot table which gives you the total customer references according to the existing Month slicer choices.
In cell S1 there's a formula giving the same data from your pivot table.
If you select any cell in the reference column of your pivot table and then go to the ribbon, Pivot table tools, Options, Active Field section, and click on Expand/Collapes Entire field you'll see just the subtotals and Grand Total.

BTW, your long formula (to get quarters) of:

=IF(MONTH([@[Comp Date]])=1,[@Year]&"-Q3",IF(OR(MONTH([@[Comp Date]])=3,MONTH([@[Comp Date]])=4,MONTH([@[Comp Date]])=5),[@Year]&"-Q4",IF(OR(MONTH([@[Comp Date]])=6,MONTH([@[Comp Date]])=7,MONTH([@[Comp Date]])=8),[@Year]+1&"-Q1",IF(OR(MONTH([@[Comp Date]])=9,MONTH([@[Comp Date]])=10,MONTH([@[Comp Date]])=11),[@Year]+1&"-Q2",IF(MONTH([@[Comp Date]])=12,[@Year]+1&"-Q3","")))))

can be shortened to:

=[@Year] & "-Q" & CEILING(MOD(MONTH([@[Comp Date]])+6,12)+1,3)/3

and the current formula (for the month) of:

=CHOOSE(MONTH([@[Comp Date]]),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

can be shortened to:

=TEXT([@[Comp Date]],"mmm")

[steve400243]

Thats great, I knew it had to be in the field selection but just could not find it. Thank you for your time in looking at the file. I really appreciate it. Thanks also for tips on the long formulas, I was going to look at that also. Have a great weekend!!