excel - tabulate frequency counts including zeros

Question

To illustrate the problem, consider the following data: 1,2,3,5,3,2. Enter this in a spreadsheet column and make a pivot table displaying the counts. Making use of the information in this pivot table, I want to create a new table, with counts for every value between 1 and 5.

1,1
2,2
3,2
4,0
5,1

What is a good way to do this? My first thought was to use VLOOKUP, trapping any lookup error. But GETPIVOTDATA is apparently preferred for pivot tables. In any case, I failed with both approaches.

To be a bit more specific, assume my pivot table of counts is "PivotTable1" and that I have already created a one column table holding all the needed lookup keys (i.e., the numbers from 1 to 5). What formula should I put in the second column of this new table?

Answer 1

So starting with this:

To illustrate the problem, consider the following data: 1,2,3,5,3,2. Enter this in a spreadsheet column and make a pivot table displaying the counts.

I then created the table like this:

X | Freq                                         
- | ---------------------------------------------
1 | =IFERROR(GETPIVOTDATA("X",R3C1,"X",RC[-1]),0)
2 | =IFERROR(GETPIVOTDATA("X",R3C1,"X",RC[-1]),0)
3 | =IFERROR(GETPIVOTDATA("X",R3C1,"X",RC[-1]),0)
4 | =IFERROR(GETPIVOTDATA("X",R3C1,"X",RC[-1]),0)
5 | =IFERROR(GETPIVOTDATA("X",R3C1,"X",RC[-1]),0)

Or, in A1 mode:

X | Freq                                     
- | -----------------------------------------
1 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",F3),0)
2 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",F4),0)
3 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",F5),0)
4 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",F6),0)
5 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",F7),0)

The column X in my summary table is in column F.

Or as a table formula:

X | Freq                                       
- | -------------------------------------------
1 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",[@X]),0)
2 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",[@X]),0)
3 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",[@X]),0)
4 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",[@X]),0)
5 | =IFERROR(GETPIVOTDATA("X",$A$3,"X",[@X]),0)

That gave me this result:

X | Freq
- | ----
1 | 1   
2 | 2   
3 | 2   
4 | 0   
5 | 1