# How to get total amount for each client in a list automatically?

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

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