A solution for providing a "sum" operation on a calculated field in a SharePoint view

One of our site owners recently ran into a situation in which he was trying to create a SharePoint view that would provide a sum of all values contained within a calculated field, but was not able to do so via the simple configuration process.  By "simple configuration" process, I'm referring to the typical way that you would provide a sum on a standard number or currency field which would be to place your view in edit mode, scroll down to and then expand the Totals group, locate your field, select the "Sum" operation listed in the drop down control, and save the change to your view.  Unfortunately, this isn't possible for calculated fields due to the fact that calculated fields don't show up under the Totals group.  With this in mind, a few possible solutions that would provide the requested functionality are listed as follows:

• Use a Content Query Web Part to host the XSLT code that would generate the desired view
• Use SharePoint Designer to modify the XSLT code of the view directly.  In SharePoint 2007, you would do this by inserting a Data View web part and modifying the XLST of that web part. 
• Change the calculated field to a regular number or currency field, add a workflow to the list that would perform the calculations previously associated with the calculated field, and place a sum operation on the field

After a discussion with the user about the requirements for this effort as well as the various options available, we came to the conclusion that it would be possible to use a simple, one-time solution to meet the needs of his effort.  In light of this, we chose the workflow option and decided to add our workflow to the list using SharePoint Designer.  If you're interested in this solution, here are the steps we took to make this happen:

Replace the calculated field with a standard number or currency field

1. Using your browser, open the site and navigate to the list to be modified
2. Under List Tools, click on the List tab
3. Click on List Settings
4. Under Columns, locate your calculated field and click on it's link
5. Record the formula for your calculation
6. Click Delete to remove the column
7. Click on Create column
8. For Column name:, enter the name of the original calculated field that you just deleted
9. Select either the Number or Currency radio button depending on which you require
10. Click OK

Create and publish the calculation workflow using SharePoint Designer

1. Open Microsoft SharePoint Designer 2010
2. Click Open Site
3. For Site Name:, enter the URL of the site your list is contained under
4. Click Open
5. Under Navigation, click on Lists and Libraries
6. Click on the list you wish to perform the calculations on
7. Under Workflows, click on the New... icon
8. Add an appropriate Title and Description for you workflow
9. Click OK
10. Once the Workflow Designer appears, click on the Action button
11. Under Core Actions, select Do Calculation
12. Click on the first value link
13. Click on the function icon
14. For Data source:, select Current Item
15. For Field from source:, select your first field that is part of the calculation
16. Click OK
17. Click on the plus link and choose the appropriate option for your calculation
18. Click on the second value link
19. Click on the function icon
20. For Data source:, select Current Item
21. For Field from source:, select your second field that is part of the calculation
22. Click OK
23. Click on the Action button
24. Scroll down to List Actions and click on Set Field in Current Item
25. Click on the field link and select the field you just created previously that replaced the original, calculated field
26. Click on the value link
27. Click on the function icon
28. For Data source:, select Workflow Variables and Parameters
29. For Field from source:, select the Variable: calc item
30. Click OK
31. If needed, continue adding calculation steps until the workflow is complete
32. Click the Save button
33. Under Navigation, click on Workflows
34. Click on the link associated with your newly created workflow
35. Under Start Options, remove the check from the Allow this workflow to be manually started option
36. Place a check in both the Start workflow automatically when an item is created and Start workflow automatically when an item is changed options
37. Click the Save button
38. Click the Publish button

Update your view to provide a sum on the new field

1. Using your browser, open the site and navigate to the list to be modified
2. Under List Tools, click on the List tab
3. Click on List Settings
4. Under Views, locate your view and click on it's link
5. Scroll down to and then expand the Totals group
6. Locate your new field and select the "Sum" operation listed in the drop down control
7. Click Save

With these steps complete, the new "calculated" field will be updated by the workflow every time a list item is created or modified.  NOTE: Users will be able to enter values into the "calculated" field; however, the workflow will ultimately override this value when the items is created or saved.  At this point, you will now have a "sum" operation on your "calculated" field.