Pivot table with columns listing counts

**theThirdMan** · 12-10-2012, 05:27 PM

I have a spreadsheet similar to this:

Name........Cat....Dog....Fish....Mouse
NY............Cat.....Dog....Fish..........
NY............Cat...............Fish....Mouse
DC.....................Dog..............Mouse
DC...............................Fish............
CA ...........Cat................................

I want to create a pivot table to do this:

Name.........Cat....Dog....Fish....Mouse
NY...............2.......1.........2........1..
DC...............1......1..........1............
CA ...............1.............................

I have been unable to get it formatted correctly. The closest I get are "sub-colums" with counts. Anyone have any idea how to go about this?

Thanks in advance!

**MarvinP** · 12-10-2012, 05:51 PM

Hi 3rd,

Your data is set up wrong for a pivot table. You have what I call a CrossTab format to your data. See how I've changed it around and done what I think you want.

**theThirdMan** · 12-10-2012, 06:20 PM

Thanks for the response Marvin,

The issue with your solution is that I am unable to modify the layout of the table and it needs to be dynamic to updating (it grabs the data from another report).

I have almost come up with a solution, adding a new column for each "animal" that adds 1 or 0 if the animal is present in the linked cell or not. The issue here is that I cannot use the SUM function in the pivot table from linked cells. I may just need to add to the macro that grabs the data, pasting the value as a number over the original formula. But I would rather not do that as it takes a while to convert.

Any other solutions?