Pivot problem - Sum of records by months

[ravikiran]

Hi Gurus,

I have an problem with pivot. Ashamed of not to be able to get the thing working. But hopefully one you might enlighten me.

Problem data:
Month Sales Person Quantity
Jan Smith 101
Jan Kiran 125
Jan Smith 111
Jan Smith 110
Jan John 149
Jan Laura 145
Feb Smith 102
Feb Laura 120
Feb Kiran 117
Feb Laura 134
Feb Laura 107
Feb Smith 111
Mar Laura 125
Mar Smith 149
Mar Laura 139
Mar John 108
Mar Kiran 121
Mar John 101
Mar Laura 115
Mar Ravi 125


Output table: I need to be able to use pivot on the above data and generate Total qty per person per month in columns. Able to convert it into following output. Will I be able to do this using pivot?

SP Jan Feb Mar Total
John 149 0 209 358
Kiran 125 117 121 363
Laura 145 361 379 885
Ravi 0 0 125 125
Smith 322 213 149 684

I also attached a sample file for clarity. Sample Pivot.xlsx

Thanks in advance for all the help.

cheers,
Ravi.

 

[Luke M]

Yes, you can do a Pivot. You were so close...
Click on PivotTable, right-click, Show FieldList. In Field List, drag "Month" to Column Labels. That's it!

Keep/Delete the slicer as desired.

 

[ravikiran]

Thank you very much Luke. I am sure I tried doing that and thought it isn't working. Probably I am sleepy

Thanks again.

cheers,
Ravi.