Pivot table not showing Row Total

Question

Hi All

I have a dataset that has a column of names and each row contains 1 item per row of which are in either, say Blue, Red, Yellow or Green

In this case the items are dates. I want the pivot table to count how many dates appear for each column for each name which is no problem.

But what' I'm having problems with is the Row Total.

I searched other posts that suggested adding a Calculated Field but it comes up with a 0 and if I format the items to Dates it comes up with a very odd number. I tried =SUM(Blue+Red+Yellow+Green) but that didn't work either.

I just want to count how may  Dates per name in Total. I've also tried to convert all the dates to '1' but it still doesn't count it.

This is of course an example because the dataset I'm using is huge and comes out of an MIS report.

Thanks for any tips.

Answer 1

Hi Jen,

Since your issue involves Pivot Table, your query will be best addressed in TechNet forum for Excel IT Pro mentioned below.

http://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

Hope the link provided is helpful.

Thank you.

Answer 2

My suggestion is restructuring the format of your data. You need one column for 'Colour' and one column for 'Date' (or watever the data might be). Example data table is as below:

ID	Name	Colour	Date
Pig	Jo	Blue	17-Dec-13
Dog	Jack	Blue	08-Jan-14
Cat	Sue	Green	09-Jan-14
Pig	Jo	Red	17-Jan-14

Then in a Pivot table you can have Name in Rows, Colour in Columns, and count of date in Values:

 

Answer 3

Thank you for your help.

I can't change the data because that is how it comes out from our MIS. The reason for the pivot table is to make it easier for some of our users who have very little excel skills I was hoping to do a template so that all they need to do is paste from the export file into the table in place and just refresh the pivot to translate the way it is exported from the other program.

What did work however is having a protected column outside the pivot  immediately to the right which has a formula that does the job.

But I need to show in the pivot the ID along side the name. Is there a way of adding a field in the pivot that is not used in the  calculation but needs to be there for info? For instance if the name and forename were in different columns, you need both.

Answer 4

Hi,

I quite agree with Ahmad's suggestion of rehashing your data layout but if you do not want your end user in spending time on this, then you can dynamically do so with Power Query.  Here is a video - http://www.ashishmathur.com/converting-a-matrix-data-layout-to-a-tabular-layout/

Hope this helps.