Posted on December 3, 2012|by: Ross Beurmann| 21 Comments

MetroStar has updated this blog, click here to see the newest version.

Applies to:  SharePoint 2010

About Summing Calculated Columns

Calculated columns are great, but if you expect to be able to total calculated columns using the Totals feature in the View Settings, think again.  To sum calculated columns in a list you need SharePoint Designer 2010, a web part page, a data source, and a line of code.

Example Overview

This example will total the results of a calculated field in a custom list. The list will be inserted as a data source on a web part page.  The list and web part page have been previously created and the creation of these items is not documented in the demo.

On a web part page called Demo Calculated Columns.aspx, an empty data source will be added with four columns inserted from the calculatedcolums list as the data source:  Title, column1, columns2, and 1and2.

Creating a Totals Field for a Calculated Column

 1.       Create the list
This demo does not include screenshots or instructions for building the list used in this example.  To recreate this demo, create a new custom list and add the three columns outlined in the Example Overview section.

2.       Create a web part page
This demo does not include screenshots or instructions for creating the web part page used in this example.  To recreate this demo, create a web part page somewhere in your test site. 

3.       Add an empty data source and configure

4.       Add XSL Value of Select data

Add an Empty Data Source and Configure

 1.    Open the Demo Calculated Columns.aspx page in Advanced Mode in SharePoint Designer in Split mode.  In the design portion, click and place your cursor in the PlaceHolderMain section of the page.  From the Ribbon, on the Insert tab, select Data View, Empty Data View

2.       Select Click here to select a data source and choose calculatedcolumns.  The Data Source details pane will open on the right.  Insert the following fields into the new data source:  Title, column1, column2,and 1and2.

3.  The totals information should be included in a new row at the bottom of the list.  To insert a new row, in the last cell of the table, right-click and select Insert, Row Below.

4.        Your cursor will now be in the last cell on the bottom row of the table in the design pane.  In the code pane, the cursor will be placed in its current cell position. 

 Add “XSL Value of Select” Data

1.     Continuing from step 4 above, we want to delete the current code of the new cell.  For the active cell, copy the code snippet <td><xsl:text xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime” ddwrt:nbsp-preserve=”yes” disable-output-escaping=”yes”>&amp;nbsp;</xsl:text> and delete it. 

2.       Input the following code (replace the highlighted text with your column name) and Save your page: 
Note – Designer is precise when it comes to code.  If you copy and paste the row of code below, after pasting into Designer, delete the in the code pane, then re-add them.   Otherwise, due to font differences, a XSLT style sheet error will display.
<td ><xsl:value-of select=sum(/dsQueryResponse/Rows/Row/@_x0031_and2) /></td>

3.       The calculated column is now totaled. 
 

Additional Modifications

Add the Toolbar

So that users can interact with the data source by adding new items, setting alerts, and filtering and sorting on column headers, add the SharePoint Toolbar. 

1.     To add the Toolbar, from the Ribbon, on the Design tab, in the Toolbar section, click Options, then select SharePoint ToolbarSave the page in Designer.  Note:  Alternatively to only enable sorting and filtering on column headers, leave the Toolbar setting as is and in the Show/Hide section check Sort & Filter on Headers.

2.       The data source now displays with the Toolbar.

Add the Quick Launch to the page

So that users can navigate easily around the site, add the left hand naviation, the Quick Launch, to the web part page.

1.  Delete the following lines of code:
<asp:Content ContentPlaceHolderId=”PlaceHolderNavSpacer” runat=”server”></asp:Content>
<asp:Content ContentPlaceHolderId=”PlaceHolderLeftNavBar” runat=”server”></asp:Content>
<style type=”text/css”>body #s4-leftpanel { display:none;}.s4-ca { margin-left:0px;}</style>

 2.       The Demo Calculated Columns page now displays with the Quick Launch.

Filed in: Technology, Tips & Tricks

20 Comments

  1. Sqwrcene Davis
    January 20, 2013 at 10:10 pm

    I follow the steps for the calcualting (calculated field) however I am getting zero of the total. Is there specific datatypes that the columns/fields should be in the original list?

    • Jennifer Hersko
      February 13, 2013 at 10:35 am

      If a list view page is edited in Designer, the calculated columns sums will equal zero. In order for this to work, a new Web Part Page should be created, and the list added as a Data Source to the page.

  2. Shrikant
    March 7, 2013 at 5:57 am

    Hello Jennifer Hersko,
    I have one question on sum of Calculated column.
    It return “NaN” when any row haven’t calculated value [empty].

    • Juan
      March 24, 2013 at 11:00 am

      Me too, I’m getting NaN’s everywhere… is it because I’m using currency type instead of numeric values ??

      • Big S
        October 22, 2014 at 7:27 pm

        Yes, seem that only numbers work, not the currency datatype… I am sure you could tweak the XSLT code to make it work. all i did was (since i wanted Currency) just use Number (with 2 decimals) and then put a “$” at the front… That worked for me. !

  3. Jeff
    September 5, 2013 at 5:49 pm

    For a SharePoint 2007 site, this works, but only in Datasheet view:
    1. Within view select View > Modify View
    2. Expand Totals section
    3. Select something other than None for any column
    4. Click OK
    5. Actions > Edit in Datasheet
    6. In bottom row, select cell of calculated column you wish to sum
    7. If you wish, remove the total from step #3 above

    (I don’t know another way to display the grand total row except by steps 2-4, or within SharePoint Designer.)

  4. Jovix
    September 16, 2013 at 1:24 pm

    What if you wanted to do like an aggregate function and sum by unique values from another field. any ideas on how to do that?

  5. October 4, 2013 at 5:02 pm

    Hi Jenny,

    I have the same problem as Shrikant and Juan. When I add the line of code I get NaN. If I add a ‘.’ at the end of the column name I get the total to go to ‘0’ zero but of course that is not correct. I have been searching for a week to find a solution to this problem and have come up with nothing. Different methods tell me to add a column and perform the calculations in the web page part. I have the same results as using your method.

    I am stumped. Please help if you can :)

    Thanks, Sherri

  6. sophie
    November 12, 2014 at 1:15 am

    thanks for your sharing, and it’s very useful to me. but here is one question it comes: it can only total 1-10 or 11-20 etc. every 10 pieces data total not all my 11454 lines of total. what should i do with this?
    looking forward to your reply.

  7. November 19, 2014 at 10:57 am

    Change the view limit looks like your web part is only showing 10 items at a time, since this is an XSLT function it will only total what is shown in the view.
    Thanks
    Ross

  8. TP
    December 19, 2014 at 1:58 am

    I have tried to use your method with SharePoint 2013 but I am having trouble with sum function.

    tp

    • December 26, 2014 at 11:47 am

      TP,
      Try to open the page in SPD10 and add the script that way.
      Thanks
      Ross

  9. Mona
    April 14, 2015 at 5:00 am

    how to apply same thing in SP designer 2013 coz there is no data source picker.

  10. Wolfgang
    July 16, 2015 at 9:13 am

    Hi, I’m getting the NaN as a summary results as well. But I observed that this is caused only, if the totals are >= 1,000 as it uses the thousand-comma separator. As soon as I enter values way lower than that, the displayed numbers show correct values.
    So seems to be a formatting issue…but I have no clue how to fix.

  11. CE
    December 30, 2015 at 12:36 pm

    How can I modify the code for column 1and2 to subtract column 2 from the total?

  12. July 5, 2016 at 4:42 pm

    For those of you who got “0” as your total, I had the same problem. It was an easy fix in my case once I figured it out. Jenny says to replace the highlighted text with our column name in the line of code below. Note she uses a column name of “1and2” in her example. But in the line of code it’s “_x0031_and2”. She uses the hexadecimal code for the number 1 instead of the 1 itself. So if you are adding the underscore X before your field name like I did, you will get “0”. I hope this helps someone.

  13. July 5, 2016 at 4:44 pm

    Thank you Jenny for this post! It worked! :-)

  14. September 7, 2016 at 4:03 pm

    I spoke too soon. This seems to only sum the totals for the current number of items/rows in the group. But I will be adding new rows continually and only 10 items are showing per page. How do you get the total to do a grand total over ALL the rows as you add new rows?

    Also, how do you get it to show more than 10 records?

    Thanks!

  15. muriel greenwood
    September 12, 2016 at 1:50 pm

    Just awesome topic! Unfortunately, I found this article too late – I already found the answer on another service. I mostly use http://goo.gl/u0s7Av to edit my PDFs. I think it also allows you to to create fillable pdfs and esign them.

    • Stephanie Wilson
      September 14, 2016 at 10:08 am

      Hi Muriel,

      We’re glad you enjoyed our blog. Thanks for sharing that link with us!

One Trackback +

  1. […] previously published a blog on using the DataView Web Part and SharePoint Designer to get the total on a Calculated Column. […]

Your email is kept private. Required fields are marked *

*
*