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 |
Done by code 
Leave a Reply