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 |

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),””)

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.

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

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

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”

lol don’t call it the final solution

Thank you so much. This is incredibly helpful!

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!

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),””)

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.

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

By the i have Version 4:2 of Numbers

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.

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…)

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