Total Calculated Columns in SharePoint


Out of the box SharePoint does not allow you to create totals in a SharePoint view for calculated columns or calculated fields.

Here is a work-around to total a SharePoint Calculated Columns or the Calculated Fields. (Note this will also create subtotals when using Group By options in a standard list view.)

Let’s say we have a field called PROFIT that is a calculated field set to subtrack the expense field from the revenue field in our list. ([Revenue]-[Expense])

  1. Create a regular number field (or currency field) called TOTAL PROFIT (or any other name you want) in that list.
  2. Using SharePoint Designer create a workflow for your list that runs when list items are created and when list items are changed.
  3. The workflow will have only one step name it something like set TOTAL PROFIT.Leave the condition field blank.
  4. In the action section choose “Set field in the current item”.
  5. Click on the word “field” and choose TOTAL PROFIT (the name of our column).
  6. Click on the word “value” and click on the “fx” button to do a lookup.
  7. Choose “Current Item” for the source.
  8. Choose “PROFIT” for the field. (This is the calculated column.)
  9. Click Finish.

Now whenever someone creates or modified a list item in that list the non-calculated field will automatically be updated with the data from the calculated field.

This means that by adding the TOTAL PROFIT field to any view you can total it and get a sum or any other calculation since you are no longer using a calculated field (directly).

Hope this helps someone,
Richard

Advertisements
Explore posts in the same categories: SharePoint 2007, Workaround

Tags: , , ,

You can comment below, or link to this permanent URL from your own site.

21 Comments on “Total Calculated Columns in SharePoint”


  1. […] This could also be easily used to create a column that calculated the season as an example to give them both the ability to maybe group quarterly results and total them. (Note: To total a calculated column see my total calculated columns post.) […]

  2. aleksi Says:

    The workaround works, but it adds an annoying feature when editing the list items. For some reason the workflow takes some time (even 10 of minutes!), so during the time the workflow is in progress if the user tries to update the data of the list item the user only gets save conflict. This is really annoying when editing the list in a spreadsheet format, the system constantly makes saves so the users has save conflicts all the time.

    Does anyone know why the workflows take so much time? I guess it should not take since the operation is really simple. Is there any way to “fix” this behavior?

  3. Martin de Bruin Says:

    I have the same issue, this is very, very annoying, does anybody has a solution to workaround this error ?

  4. Paolo Says:

    Mine works perfectly fine, almost real time from the time i updated and create the item.. Maybe its in your connection / network speed?

  5. mike g Says:

    I had the same problem, a conflict error was received when I made multiple changes to list items within the same minute or minute after.
    I made adjustments to the workflow to pause for 65 minutes after an item is created or changed. After 65 minutes then kick the workflow to input the value in the field.

  6. Mark Says:

    Works fine for me as well…Thanks for the post Richard!

  7. Geekavenger Says:

    I believe this will cause an infinite loop. If you have versioning on your list this could cause perfomrance issues. Basically right now how it works is: Change>set Total Profit (which counts as a Change)> set total profit>Set total profit… and so on. A solution would be to add the condition: If Profit Does not Equal Total Profit Update Total Profit Else Stop Workflow Log Total Profit Accurate.

    • JSalt Says:

      Thanks Rick – worked great and solved a very annoying problem. I did get into an infinite loop condition though. My site went from 24MB to 1 GB before I discovered thousands of new versions of files that triggered the workflow. Adding the condition suggested by Geekavenger (thanks!) fixed it for me.

  8. rharbridge Says:

    Unless I am mistaken that is not correct. Set field in current item does not throw an item update event. So it would not continue. If you use update list item it would be infinite.

    Hope this helps,
    Richard

  9. Artur Says:

    You may also use Malcan Workflow for SharePoint which allows you to do it in seconds. You may easily calculate values not only based on row values, but based on other lists, lookups, build own algorithms. All in browser.

  10. Derek Lewis Says:

    Richard, thank you so much – great workaround solution and completely solves my problems. Thanks again.

  11. Eric Stepek Says:

    Excellent Tip! Exactly what I needed.

  12. Steve Soper Says:

    Brilliant! Thanks for taking the time to post this solution.

  13. Madhavan Says:

    Great Post saves my time!!!


  14. Appreciate it! It did work (unlike a lot of things i try)

    However is there a better way? It sure seems like i should be able to do VERY simple excel like equations much simpler in sharepoint.

    It just creates so many different tasks; modifying your views etc.

    • rharbridge Says:

      It really does. It’s one of those constant important things to remember… Sometimes working in Excel is MUCH better and more efficient than working within SharePoint.

      In this example you need to weigh how much effort is inolved, and how much benefit SharePoint gives over Excel in that scenario.

      In 2010 Office we have the ability to do alot of co-authoring and more work together in Excel which may be a good reason SharePoint isn’t the better solution at that point. Another alternative might be that SharePoint is the better solution due to improvements in how you can manipulate and work with lists.

      Certainly if these columns were metadata on documents or infopath outputted xml files then SharePoint (even with it’s challenges) makes for the better solution.

  15. Deesha Says:

    This works grt!!!
    But i am looking to do some more calculations on the SUM of column values(Ex Total Profit column SUM). Can you suggest some way by which we can store the SUM into a column??

  16. bbrenes Says:

    I am new at this and spent all afternoon looking for a solution to this same problem. Thanks a million for posting your workaround. It works!!! Exactly what I needed. Thanks.

  17. sbraun Says:

    This is perfect! Worked like a charm and no delays in the workflow completing. Thanks so much for the great tip!

  18. Phil Says:

    Make sure you are checking items in and out on your list, and adding a stop item to the workflow. Not doing this can cause workflow issues, especially when you have more than one workflow.

  19. Jennifer Says:

    Thanks! Works for me. Simple solution that works


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: