# How to mimic Pivot Table or Categories in Number 3+

 Category Amount A B 1 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.

 Category Amount Test A B C 1 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.

 Category Amount Test A B C 1 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.

 Category Amount Total per category A B C 1 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

Posted

in

by

Tags:

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

1. taylorlevy

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

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

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

lol don’t call it the final solution

6. Anonymous

Thank you so much. This is incredibly helpful!

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

8. Mike

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

9. Mike

By the i have Version 4:2 of Numbers

10. Rej

Narrowing the results down to one entry…

I modified the “Total per Category” equation in Numbers v4.3 to:
IF(COUNTIF(A\$1:A1,A2)=0,SUMIF(A,A2,Amount),”-“)
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

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

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

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

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