How to mimic Pivot Table or Categories in Number 3+

Perhaps you are already aware that Apple has cut many features from latest versions of Numbers and their other MS Office like products. One of the most popular features that has been removed from Numbers is called Categories. By activating Categories on a table you could aggregate data and summarize values see an overview of what matters to you. Recently I was working on a list of values and since the list was not bigger than what Numbers can handle and I didn’t need the speed and power of Microsoft Excel. I though I will give Numbers a try. I still like the fact that you have a free canvas in Numbers that allows you to put put many tables independent from each other in one page. I think it is the only advantage of Numbers to its alternatives. Otherwise with just a few dollars per months you could have access to the latest version of Microsoft Office regardless of your OS or even directly in your browser plus Microsoft gives you a terabyte of online storage! OK, let’s get back to work before I change your mind 😉

Let’s start with an example like the following.

1 Category Amount
2 Blue 11.20
3 Red 15.89
4 Red 10.30
5 Orange 32.12
6 Green 15.39
7 Blue 10.18
8 Green 24.76
9 Green 89.31
10 Orange 8.75
11 Blue 15.28

The first column is just containing the order of each row, the second indicates the category of each row. This could be anything like category of expenses in an expense report. It is actually the column that we are going to aggregate. The third column contains the values. It could be the amount of each expense for example.

Now, let’s say you want to aggregate it to the following table. But you want Numbers to do it for you so each time a series of row is added to the table you don’t need to calculate everything manually.

Category Sum
Blue 36.66
Red 26.19
Orange 40.87
Green 129.46

Since I want to do the aggregation in the same table, I need to find a way to detect distinct categories. Then per each category I need to calculate and display the sum of each category in front of it. If I display only the first occurrence of each category and hide other occurrences the problem would be solved. Let’s split the problem to smaller pieces as usual. Now the first question would be how do we find distinct categories?

Step 1 – Detecting distinct categories

Let’s add a new column called “Test” then put the following formula in the first cell of the column below the header: =COUNTIF(A$1:A1,A2) in other words you need to put it in C2 cell. Now click at the bottom of the cell’s rectangle and drag it to the bottom of the table. This will fill remaining cells of the column. The result will be the following table.

1 Category Amount Test
2 Blue 11.20 0
3 Red 15.89 0
4 Red 10.30 1
5 Orange 32.12 0
6 Green 15.39 0
7 Blue 10.18 1
8 Green 24.76 1
9 Green 89.31 2
10 Orange 8.75 1
11 Blue 15.28 2

As you can see, you only get zeros for the first occurrence of each category and that means we can detect them with an IF. Now, let’s put the following formula in cell C2 instead: =IF(COUNTIF(A$1:A1,A2)=0,"*","") and as before drag the corner of the cell until the bottom of the table which will give us the following result.

1 Category Amount Test
2 Blue 11.20 *
3 Red 15.89 *
4 Red 10.30
5 Orange 32.12 *
6 Green 15.39 *
7 Blue 10.18
8 Green 24.76
9 Green 89.31
10 Orange 8.75
11 Blue 15.28

Now for first occurrence of each category we are displaying “*” in column C.It means that we are able to detect the first occurrence of each category and display an arbitrary text in it. What about displaying SUM of each category there. This would be the final solution.

My final solution

In this step we are going to replace the “*” in the formula with SUMIF(Category,C2,Amount) this way the SUM of each category will be calculate in front of each occurrence of that category. All you have to do is to put the following formula in C2, then drag the bottom-right corner of the cell until the bottom of the column.
The resulting table will be similar to the following. I just renamed the Test column to “Total per category”. You can use formatting to distinguish the aggregate row or even hide other rows if you like.

1 Category Amount Total per category
2 Blue 11.20 36.66
3 Red 15.89 26.19
4 Red 10.30
5 Orange 32.12 40.87
6 Green 15.39 129.46
7 Blue 10.18
8 Green 24.76
9 Green 89.31
10 Orange 8.75
11 Blue 15.28






17 responses to “How to mimic Pivot Table or Categories in Number 3+”

  1. taylorlevy Avatar

    Thanks so much for this explanation. Noticed a small mistake in the last formula. The second C2 should be A2, so that it properly references your current category. Like this: =IF(COUNTIF(C$1:C1,C2)=0,SUMIF(Category,A2,Amount),””)

  2. Reza Avatar

    You are welcome and thanks for your correction. It’s fixed now. I might also upload a sample spreadsheet if I find a little bit of time in the coming days.

  3.  Avatar

    Brilliant; but how might I get the pie chart to ignore the “0”, if I have a huge table and I just chart “A versus C” in your example

  4.  Avatar

    Brilliant; but how might I get the pie chart to ignore the “0”, if I have a huge table and I just chart “A versus C” in your example

    1. Reza Avatar

      To be honest whenever I have a large data set I switch to Excel. I couldn’t get the charts to filter their data source in Numbers. See if this can help “Modify chart data references in a Numbers spreadsheet”

  5.  Avatar

    lol don’t call it the final solution

  6.  Avatar

    Thank you so much. This is incredibly helpful!

  7. Lindsey Schuh Avatar

    Does my equivalent to “Category” and “Amount” need to be in quotations or something….I can’t get the final formula to work at all. Also my data doesn’t start in row 2 like your example but in row 5 so I tried it with your formula and attempted equivalent ranges for my document and still error messages. Please help!

    1. Lindsey Schuh Avatar

      I figured it out finally! I was trying typing in the header name for the column in and out of quotations and in the last step you had changed the beginning from =IF(COUNTIF(A$1:A1,A2)=0 instead to =IF(COUNTIF(C$1:C1,C2)=0 so I got really confused. The following formula is what actually ended up working for me (Of course I had to change the column letters and row numbers to fit where my data started and where I wanted to pull it from but this is the equivalent to your example).


      1. Reza Avatar

        Sorry, I just got back from vacation. Glad you figured yourself. I will share an example soon so people can download and play with it. This would make learning easier.

  8. Mike Avatar

    Cannot get the Final Solution Formula to work (cannot type category)

  9. Mike Avatar

    By the i have Version 4:2 of Numbers

  10. Rej Avatar

    Narrowing the results down to one entry…

    I modified the “Total per Category” equation in Numbers v4.3 to:
    so that that column “C” either contains the sum or a “-“. Next, hide column “B” . Then, “Filter” the results to display just the numbers, which makes the pie chart look nice.

  11. Josh Avatar

    In version 4 at least SUMIF looks easier:
    SUMIF(, , )

    Say I have a dataset as follows (line numbers are implied)

    || A || B ||
    | 1 | oranges |
    | 4 | apples |
    | 8 | oranges |
    | 3 | pears |
    | 2 | apples |

    To total each fruit time, a table with the following values, staring on line 10
    || A || B ||
    | oranges | =SUMIF($B$1:$B$5, A10, $A$1:$A$5) |
    | apples | =SUMIF($B$1:$B$5, A11, $A$1:$A$5) |
    | pears | =SUMIF($B$1:$B$5, A12, $A$1:$A$5) |

    Will result in (from line 10 again) showing
    || A || B ||
    | oranges | 9 |
    | apples | 6 |
    | pears | 3 |

    (Hope the wiki table markup works…)

  12. Rija Avatar

    Josh’s tips works great with Numbers 4.3 but when the category list is long, the following formula make it easier:

    SUMIF(Table 1::$’Bucket’,$’Bucket’,Table 1::$Amount)

    assuming the table with data is like:
    Table 1:
    | Bucket | Amount |
    | A | 3 |
    | Z | 5 |
    | A | 4 |
    | D | 1 |
    | D | 6 |

    that formula is to be entered on the first row of the summary table, each argument marked with “Preserve Column” and then drag the selection box with yellow dot all the way down to cover all the categories.

    Summary table:
    | Bucket | Amount |
    | A | SUMIF(Table 1::$’Bucket’,$’Bucket’,Table 1::$Amount)
    | B | |
    | C | V

    | Z | __

  13. Bruce Bailey Avatar

    Thank you so much.
    I have been tearing my hair out over this problem.
    Internet forums are full of users calling out for this functionality to be added to Numbers.
    Even Excel for Mac 2016 has lost pivot tables.
    The trite explanation, you get on Apple forums, that most users don’t require advanced functionality is infuriating.
    In many ways it seems software is getting dumber.
    Thankfully with communication getting better, solutions can be sourced.

  14. C S neville Avatar
    C S neville

    just switch to excel or the free fibre office.. numbers is a piece of shit!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: