Count Row and Column in Pivot Table
Hi I have a sheet that collects the following
++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Name + Address + Borough + Postcode + Date Added +
++++++++++++++++++++++++++++++++++++++++++++++++++++
John blah.. London XX12 XXX 21/01/2012
Roger blah.. Surrey ZZ14 ZZZ 18/02/2013
Mary blah.. Surrey EE17 EEE 18/02/2013
Helen blah.. Hackney VV25 VVV 19/05/2014
++++++++++++++++++++++++++++++++++++++++++++++++++++
And I have a Pivot Table with Borough as the Column and Date Added as the row (which ive grouped into months & years), which makes something like this:
++++++++++++++++++++++++++++++++++++++++++
+ Date Added + London + Hackney + Surrey +
++++++++++++++++++++++++++++++++++++++++++
+ 2012 + + + +
+ Jan + + + +
+ 2013 + + + +
+ Feb + + + +
+ 2014 + + + +
+ May + + + +
++++++++++++++++++++++++++++++++++++++++++
My question is how do I make it count or display the figures from the data for example like this below as my current table shows blank fields like above?
++++++++++++++++++++++++++++++++++++++++++
+ Date Added + London + Hackney + Surrey +
++++++++++++++++++++++++++++++++++++++++++
+ 2012 + + + +
+ Jan + 1 + 0 + 0 +
+ 2013 + + + +
+ Feb + 0 + 0 + 2 +
+ 2014 + + + +
+ May + 0 + 1 + 0 +
++++++++++++++++++++++++++++++++++++++++++
Solution 1:
In the values field for the pivot table, you need to insert what you want to sum or count. Then click it to select what calculation you want it to perform (sum, count, etc).
Solution 2:
I was confused by the previous answer, so here's finally worked for me. I had data original data like so:
01/01/2015 /r/foo
01/01/2015 /r/foo
01/02/2016 /r/bar
And so on, and I created a pivot-table that look like so:
/r/foo /r/bar /r/fizz
01/01/2015
01/02/2016
And I wanted to count each instance of each row per month. In your pivot table field, right-click the value that you want to sum the instances of like so:
And it filled in my chart:
/r/foo /r/bar /r/fizz
01/01/2015 2 0 0
01/02/2016 0 1 0