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

If performance is not a major concern, you can bypass the pivot table and use the COUNTIF() function.

Create a list of all consecutive numbers that you want the counts for and use COUNTIF() for each of them with the first parameter being the range of your input numbers and the second being the number of the ordered result list:

    A    B    C    D
1   1         1    =COUNTIF(A:A,C1)
2   2         2    =COUNTIF(A:A,C2)
3   3         3    =COUNTIF(A:A,C3)
4   5         4    =COUNTIF(A:A,C4)
5   3         5    =COUNTIF(A:A,C5)
6   2

Answer 2

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