1

Let's say I have a long list of clients in one column of a Numbers table, most of them occurring multiple times, and in the adjacent column are amounts due for services received, as depicted below. What is the easiest way to automatically add up all the amounts for each client and have them appear either in a third column or in a new table next to his or her name? That is, for Ava I would like the total of 300 + 800 + 1200 + 1600 = 3900 to appear somewhere next to her name, for Chloe it should be 450 + 950 = 1400, and so on.

enter image description here

Ideally, it should require as little maintenance as possible. For instance, if a new client is added to the list, a new entry/row should automatically appear in the third column/new table with that person's name and the sum of all their amounts.

2

This works with the formula sumif(). In your example the formula would be something like this: SUMIF($A$2:$A$22,A2,$B$2:B22). Example table

1

A few days after posting this question, I stumbled upon a post in the Apple support communities which describes exactly my problem. Two solutions are offered there. For the sake of completeness, I'll reproduce here the one I deem superior.

The solution requires adding an extra column to the table to hold the 'client number' which can be calculated via

IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")

Insert this formula into the first cell in the new column and simply drag down to get something like this.

enter image description here

Next, create a second table with two columns and at least as many rows as there are unique entries in the first table. To copy all unique client names from the first table to the left column of table, add this formula to its first cell and drag down.

IFERROR(INDEX(Bookkeeping::$Name,MATCH(ROW(cell)−1,$Client,0),column-index,area-index),"")

Finally, add this formula to the first cell of the second column and drag down to get the total amount for each client.

SUMIF(Bookkeeping::Name,A2,Amount)

The resulting second table should look something like this.

enter image description here

The nice thing about this solution is that adding a new name to the first table will automatically result in a new entry to the second table with that same name and the sum of all associated amounts.

The numbers file from which these screenshots were taken can be obtained here.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .