I have an issue. I have two lists here is schema:

First list:

 Name | Count | Sum
 asd  |   2   | 11
 wda  |   1   | 7

Second list:

LookUp/Name | Value
    asd     |   5
    asd     |   6
    wda     |   7

I solved the first part and get the count column on first list by using LookUp field and then pick Count Related option. I would like to use option like Sum Related but it dosen't exist. I can make it work programmatically and even from designer. Does anybody face similiar problem and deal with it ?


You need script to achieve that:

  1. Get all related items from Second list;
  2. Calculate the sum value for the Values of related items;
  3. Return the sum value.

Here is a demo about how to use REST API to get related items and calculate the sum value, then use CSR to change the “Sum” value in the list view.

The first list (ListA):

  • Created a field named “Sum”. Its internal field name is “Sum”.

The second list (ListB):

  • has a lookup field named “ListA”; (internal field name is “ListA”)

  • has a number field named “Value” (internal field name is “Value”).

(Adding the script below into the first list view page.)

<script src="https://code.jquery.com/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
(function () {
    var overrideNameField = {}; 
    overrideNameField.Templates = {};
    // This action will edit the "Sum" field. Change the "Sum" to another column’s internal name.
    overrideNameField.Templates.Fields = {
        "Sum": { "View": overrideNameFieldTemplate }        


function overrideNameFieldTemplate(ctx) { 
//get the current item id.
var itemID = ctx.CurrentItem.ID;

// get the related items
var sum = getRelatedItems(itemID);

return sum;   

function getRelatedItems(itemID){
    var sum = 0;
    //var siteUrl = _spPageContextInfo.webAbsoluteUrl;
    var siteUrl = "https://<technet>.sharepoint.com/sites/<site>";
    var requestUri =  siteUrl + "/_api/Web/Lists/getbytitle('ListB')/Items?$select=Value,ListA/ID&$expand=ListA&$filter=ListA/ID eq " + itemID;
    url: requestUri,
    type: "GET",
    cache: true,
    async: false,
    "ACCEPT": "application/json;odata=verbose"
    success: function (data) {
    if($(data.d.results).length !== 0) 
        for (var i = 0; i < data.d.results.length; i++) {
            var item = data.d.results[i];
            sum += item["Value"];
    error: function () {
    return sum;


The first list (ListA):

enter image description here

The second list (ListB):

enter image description here

  • how does this script work in SharePoint Online where Microsoft have made it very difficult to add scripts to pages? – motionpotion Sep 17 '18 at 7:26
  • 1
    It can work in classic experience in SharePoint Online. – JoannaW_MSFT Sep 17 '18 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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