I haven't worked with pivot tables since Excel97 (?) so aside from losing my feel for how to solve this issue, I'm also not sure if I had ever done anything like this before.
Here's the issue...
This is an abbreviated mockup of the data:
(sorry, I can't post attachments)
<tbody>
</tbody>
The result that I am seeking is to determine the number of instances for each of the numbers in the columns.
For instance, Group C has:
1 instance of 0's
1 instance of 1's
2 instances of 4's
Visually it should look like this...
<tbody>
</tbody>
Or like this...
<tbody>
</tbody>
I am able to accomplish this using COUNTIF, however the dataset is actually very large and I was hoping that it could be done using a pivot table instead. I played around with many different ways of positioning the headings, but I can't seem to get the result that I am looking for - or determine if it is even possible (although I assume that it is - which is why I am here asking for guidance).
Could I ask for someone to steer me towards the needed solution?
(btw, I am currently using Excel 2010)
Here's the issue...
This is an abbreviated mockup of the data:
(sorry, I can't post attachments)
Group | Q1 | Q2 | Q3 | Q4 | ||
A | 4 | 2 | 3 | 1 | ||
B | 2 | 3 | 2 | 0 | ||
C | 0 | 1 | 4 | 4 | ||
D | 3 | 4 | 0 | 2 | ||
E | 1 | 0 | 1 | 3 | ||
<tbody>
</tbody>
The result that I am seeking is to determine the number of instances for each of the numbers in the columns.
For instance, Group C has:
1 instance of 0's
1 instance of 1's
2 instances of 4's
Visually it should look like this...
Option 1 | 4's | 3's | 2's | 1's | 0's | ||
A | 1 | 1 | 1 | 1 | 0 | ||
B | 0 | 1 | 2 | 0 | 1 | ||
C | 2 | 0 | 0 | 1 | 1 | ||
D | 1 | 1 | 1 | 0 | 1 | ||
E | 0 | 1 | 0 | 2 | 1 | ||
<tbody>
</tbody>
Or like this...
Option 2 | A | B | C | D | E | ||
4's | 1 | 0 | 2 | 1 | 0 | ||
3's | 1 | 1 | 0 | 1 | 1 | ||
2's | 1 | 2 | 0 | 1 | 0 | ||
1's | 1 | 0 | 1 | 0 | 2 | ||
0's | 0 | 1 | 1 | 1 | 1 | ||
<tbody>
</tbody>
I am able to accomplish this using COUNTIF, however the dataset is actually very large and I was hoping that it could be done using a pivot table instead. I played around with many different ways of positioning the headings, but I can't seem to get the result that I am looking for - or determine if it is even possible (although I assume that it is - which is why I am here asking for guidance).
Could I ask for someone to steer me towards the needed solution?
(btw, I am currently using Excel 2010)