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

Thanks for the suggestion.

This spreadsheet needs to have an overview of the results which is why I've been using a pivot to get them.

But your suggestion means you would have to  filter each name to see the result individually and when you go to view the next 200 other names how do your remember what all of them had?  You would need to copy and paste each one or something in order to have a spreadsheet that you would be able to compare the results which would take hours.

If I were to do it myself I  have a much quicker and easier way of doing it already,  

I merge the Id and Name into one column and once I have the info from the Pivot table, I convert the Pivot to a table and then I add the 'G' column to count like yours.  Then I split the Name and Id to 2 columns. Works really well.

What I'm trying to do is give other users this information without having to do it all for them. And there's no way they would be able to do this.

So it looks like I'm stuck with the task.

Hey I thought Pivot tables were supposed to be the best thing since sliced bread?

Can't quite get why it can't show a count!

Thanks anyway

:(

Answer 2

Hi TasosK

Thanks but I don't think you read my last reply.

"""........... I  have a much quicker and easier way of doing it already,

I merge the Id and Name into one column and once I have the info from the Pivot table, I convert the Pivot to a table and then I add the 'G' column to count like yours.  Then I split the Name and Id to 2 columns. Works really well""""

and """" What I'm trying to do is give other users this information without having to do it all for them. And there's no way they would be able to do this"""""

I'll stick to my method.

These users only use the very basics excel. Copy paste and maybe filter. They don't know what a pivot table is which is why I was hoping to use a template that they would only need to copy the data into the table and the pivots would be in place to see the results.

J

Answer 3

my dear jen,

I will delete all my answers...

Answer 4

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.