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.

3 Answers 3


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? Commented Nov 2, 2012 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. Commented Nov 2, 2012 at 20:55
  • Each invoice entry will update the associated PO and once all the invoices are received, the remainder should total 0. Commented Nov 2, 2012 at 20:56
  • @PirateEric And what to do if I need to edit the value in Invoices list? Thanks Commented Nov 29, 2013 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.