# Calculate SUM of column and use in another list's column calculation

Hullo,

My environment is Publishing site in Sharepoint 2010. I have a library named Invoices with the following structure

```+-----------+-----------+--------+
|  Invoice  | PO Number | Amount |
+-----------+-----------+--------+
| Invoice 1 |      1234 |     25 |
| Invoice 2 |      1234 |     25 |
| Invoice 3 |      6789 |     50 |
| Invoice 4 |      6789 |     50 |
+-----------+-----------+--------+
```

I also have a list where PO is looked up from

```+-----------+-------+----------+
| PO Number | Total | Reminder |
+-----------+-------+----------+
|      1234 |   100 | ?        |
|      6789 |   200 | ?        |
+-----------+-------+----------+
```

To calculate the 'Reminder' I need to sum the 'Amount' column in the Invoices library for each PO and then subtract from Total. i.e it needs to be something like

``````Reminder = Total - (SUM of Amount where PO Number=1234 from Invoices)
``````

I'm not sure how to enable this. Calculated column doesn't seem to work. Please help me achieve this.

-

I'd probably use a workflow on your first list to populate a field in your second list. So as invoices are added, the workflow would update a recieved field in the second list. Then in that second list, you can do a calculated colum Total - Received to get your remainder.

-
Sorry if I'm asking a basic question, but how do I keep adding the amount to the 'Received' column? What formula should I use? – PurpleShadow Nov 2 '12 at 20:47
You'd first lookup to the PO list where the current item PO equals the PO list PO number field and store that in a variable. Then you'd do a math operation to add the Invoice total of the current item to that variable and store it in a new variable. Then you'd do an update list action on the PO list and set the received column to that second variable. – Eric Alexander Nov 2 '12 at 20:55
Each invoice entry will update the associated PO and once all the invoices are received, the remainder should total 0. – Eric Alexander Nov 2 '12 at 20:56
@PirateEric And what to do if I need to edit the value in Invoices list? Thanks – Guilherme J Santos Nov 29 '13 at 14:00

The problem with workflow is that when a record in first list is removed the total in second list won't be updated. Because workflow doesn't run on delete action!

-

The issue with deletions can be solved by creating a custom permission level that prevents contributors from deleting items and including an explicit choice (checkbox) to indicate a request for deletion (or a workflow that runs on a zero amount). An additional problem exists if an item is updated, rather than added or deleted. In this case, a method to capture the original saved amount, compare it to the newly saved amount and process the adjustment may be required.

-