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.

A B
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.

A B C
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.

A B C
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.
=IF(COUNTIF(C$1:C1,C2)=0,SUMIF(Category,A2,Amount),””)
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.

A B C
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
Advertisements
How to mimic Pivot Table or Categories in Number 3+

12 thoughts on “How to mimic Pivot Table or Categories in Number 3+

  1. taylorlevy says:

    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. 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. Anonymous says:

    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. Anonymous says:

    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

  5. 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. 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).

      =IF(COUNTIF(A$1:A1,A2)=0,SUMIF(A,A2,C),””)

      1. 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.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s