Pivot Table - How to Structure/Edit Pivot Fields to Present Data in Preferred Way

Please see attached file

- "My Pivot" tab is the pivot field I created based on data in "Data" tab.
- "Ideal Pivot" shows a screenshot of a pivot table based on the same data just in an ideal format.

I need help in recreating "Ideal Pivot" tab format and structure.

Some notes on columns in Data:
- Detail column assigns a unique number to a service date. So if there 4 entries with the date 7/19/2019 - they will be assigned number 1 and so on.
- Signoff comment is included as a filter in the pivot table to remove results that have the value "Not".

- The goal as you can see in the Ideal Pivot tab, is to show the sum of charges by year, by detail (meaning by the unique number or service date) and then by day of week.
-In the Ideal Pivot, all charges for a specific detail number/day are automatically added and shown as 1 number (sum of all charges for that service date/day)

Hi there,

Please aee attached file. Is that what you are looking for?

Basically, with the Pivot table active, click on Pivot Tools in the top ribbon, then Design.
From there, Report Layout. I changed it to Tabular Form.

Hi again,
Have a look at the atached.
The issue was the formula in your data for the 'Detail' column.
I have forced the result into Text and that seems to work.

Hi rangers27,

You already had the Sumproduct formula:
The SUMPRODUCT and COUNTIF parts basically count on how many unique dates you have. This is done with an expanding range (only the first cell reference is fixed with an $ sign, the end range is growing as you pull the formula down. This gives you the unique 'Detail' sequence numbers.

The bits I have added are in blue: Telling excel that I want the result from the formula as text, formatted as '0' with is a number without any decimals. The red bit is actually not required, but I added it to double ensure the result is 'Text' with adding a 'space' at the end of the result.

=TEXT(SUMPRODUCT(1/COUNTIFS(A$2:B3,B$2:B3)),0)&" "

You could actually simplify it to:

Formula:

Please Login or Register  to view this content.

Note the range correction in the Countif part.


If your Question is answered; please mark it SOLVED. You can do this via the Thread Tools at the top of your original post.
If you are happy with a member's solution, you can say 'Thanks' and click the 'Star' to Add Reputation.