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])
- Create a regular number field (or currency field) called TOTAL PROFIT (or any other name you want) in that list.
- Using SharePoint Designer create a workflow for your list that runs when list items are created and when list items are changed.
- The workflow will have only one step name it something like set TOTAL PROFIT.Leave the condition field blank.
- In the action section choose “Set field in the current item”.
- Click on the word “field” and choose TOTAL PROFIT (the name of our column).
- Click on the word “value” and click on the “fx” button to do a lookup.
- Choose “Current Item” for the source.
- Choose “PROFIT” for the field. (This is the calculated column.)
- 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
Tags: calculated columns, SharePoint 2007, totals, Workaround
You can comment below, or link to this permanent URL from your own site.
July 25, 2008 at 1:51 am
[...] 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.) [...]
August 27, 2008 at 7:15 am
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?
September 16, 2008 at 10:08 am
I have the same issue, this is very, very annoying, does anybody has a solution to workaround this error ?
December 10, 2008 at 3:47 am
Mine works perfectly fine, almost real time from the time i updated and create the item.. Maybe its in your connection / network speed?
March 6, 2009 at 12:15 pm
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.
April 17, 2009 at 10:52 am
Works fine for me as well…Thanks for the post Richard!
April 20, 2009 at 6:41 am
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.
June 16, 2009 at 4:06 pm
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.
April 20, 2009 at 8:25 pm
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
April 24, 2009 at 8:24 am
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.
May 1, 2009 at 5:50 pm
Richard, thank you so much – great workaround solution and completely solves my problems. Thanks again.
August 19, 2009 at 6:16 pm
Excellent Tip! Exactly what I needed.
August 25, 2009 at 3:19 pm
Brilliant! Thanks for taking the time to post this solution.
August 26, 2009 at 2:44 am
Great Post saves my time!!!