How to create calculated column with data from another list

Question

I have the following situation: List A has two columns (Name, Amount) and in List B (Name) I want to add a calculated column which should be the sum of all entries in List A that have the same name as in List B. Example:

List A:

NAME      Amount
L0011     100
L0011     50
L0020     234

So in List B I want the calculated column to show:

NAME      Amount
L0011     150
L0020     234

How can this be done? Workflow (as soon as I add/mod an entry in List A, update List B) or something else? Thanks

Answer 1

lem.mallari's answer is a huge pain unless you can assume that the Amounts in List A never change, since it's not tracking whether an item has already been added to the sum. There is no way for a Workflow to iterate through a SharePoint list, which means there is no easy way to calculate the sum or average of multiple list items.

The correct way to implement this will will require some development. The SharePoint Developer Training (2010, 2013) will actually get you most of the way there: an event receiver should trigger when items are added or changed in Lists A and B that uses SharePoint's API to go through List A and average values by Name, then update all (or just affected) items in List B. Alternatively, you can use JavaScript to display the sum of all entries in List A that have the same name as the item in List B as long as all the data is displayed on your page. If you're handy with XPath and InfoPath, you could add List A as a secondary data source to List B's form and select only applicable items in List A to sum from.

But if we're talking Workflows, here's the "workflow only" method. This was tested and successful in 2010. Create custom List C with the following columns:

• Title (string, mandatory, enforce unique values)
• TotalItems (integer, mandatory, default 0)
• Sum (number, decimal places however you want, mandatory, default 0)
• Average (calculated, =IF(TotalItems=0,0,Sum/TotalItems)) (optional)

Replace the Name columns in Lists A and B with lookup columns pointing at List C. Delete the Amount column in List B, instead including the Sum column as an additional column. Add the following columns to List A, and ensure that users cannot change them directly. This can be restricted by making InfoPath forms or by making alternative view and edit forms.

• AmountArchive (number, identical to Amount, default 0)
• AmountHasBeenSubmitted (yes/no, default no)

Create a Workflow to run each time an item is created or modified in List A. Use these commands (I'm using a list for readability; it was getting ugly when formatted as code):

• If Current Item:Amount not equals Current Item:AmountArchive
  • Set Variable:Item Count to (Data source: List C; Field from source: TotalItems; Find the List Item: Field Title; Value: Current Item:Name(Return field as: Lookup Value (as Text)))
  • Calculate Variable:ItemCount plus 1 (Output to Variable: ItemCount)
  • Calculate List C:Sum (similar settings as above; be sure to use Lookup Value (as Text) and not String!) minus Current Item:AmountArchive (Output to Variable: SumWithoutValue)
  • Calculate Variable: SumWithoutValue plus Current Item:Amount (Output to Variable: NewSum)
  • If Current Item:AmountHasBeenSubmitted equals No
    • Set AmountHasBeenSubmitted to Yes
    • Update item in List C (Set TotalItems to Variable:ItemCount; Set Sum to Variable:NewSum; Find the List Item in the same way of Field:Title; Value: Current Item:Name(Return field as: Lookup Value (as Text))
  • Else
    • Update item in List C (don't do anything to TotalItems; use the same logic to set Sum to Variable:NewSum)
  • Set Amount to Current Item:AmountArchive

Answer 2

This can't be done using calculated columns because calculated columns can only be used for columns on the same list.

Using SharePoint Designer Workflows you can just use Create List Item and Update List Item actions so that whenever a user adds a value for L0011 the amount will be added in another list's column which contains the previous amounts already.

Let me know if you need a more detailed answer for the SharePoint approach and I'll provide you a step by step instruction on what to do.