Summing Calculated Columns
This is basically a re-post of something that I did on endusersharepoint.com a couple of weeks ago…
Have you ever noticed that in SharePoint views, when selecting to view “Totals”, calculated columns aren’t listed as an option for column totals? In this video, I demonstrate this dilemma, and shows how you CAN create a view that displays calculated column totals. This is done by creating a Data View Web Part in SharePoint Designer. Also, if you’ve noticed that calculated columns don’t ever show up in a data view web part, I show the fix for this, too.
Here is my video showing how to accomplish this:
Also, my buddy Mark Rackley, the SharePoint Hillbilly, has just written a blog post, that is a good supplement to what I show in the video. He shows how to create sums for groups in the DVWP.
Not sure if this might help someone else but you can also do this easily with a workflow. This was the method I used way back as I like to avoid editing pages if I can help it in SharePoint Designer. This way you can just use as many views as you want and can use all out of the box web interface options. http://sharepointkb.wordpress.com/2008/07/22/total-calculated-columns-in-sharepoint/ Great post and methodology for accomplishing the same sort of thing Laura 🙂 Richard Harbridge (Cross Posting in case anyone is looking for this info).
Sorry if this is a dumb question. But is the presentation that needs to be paid for? I get a user prompt when accessing the link and I see nowhere on the EUSP page to register.
mtodd, No, and I can get to the link fine. There aren’t usually any kind of login prompts on the eusp site.
Hey Laura, I love your posts and this one is great for what I’m doing. I also saw your post on EndUserSharepoint. I’m pretty new with DVWP, but loving it! So my question might be a stupid one…..so if I create a webpart page and design in DVWP how can I add it to my views menu? I’m also needing to do the group by and do the calculated columns that way. Cheers, Tye
Hi Tye, No, you can’t really do that. Maybe try this other post of mine, and see if that’s what you’re looking for. http://www.sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=76
Hi Laura – Thank you for your video. It was an excellent help to me. I have successfully created the column sums within my groups for my data view. However, I would like to go one step further and create an overall sum for the entire data view (a total of all the sums). I’ve tried copying and modifying the grouping sum code (removing the nodeset code and replacing it with $Rows), but I’m not getting good results. Any suggestions would be greatly appreciated. Thank you!!
Hi Laura, This is my first attempt at DVWP!!! Thanks for the very good tutorials, the only problem i have is that i am sorting my list (Holidays Booked) by “Created By” name and the count seems to display zero no matter what i do. All i am trying to do is group the holidays taken by name of employee and total the number of days they have taken. Thank after that do a calculation between number of holidays given per year minus number of holidays taken. Can you help me please as i’m finding this simple task difficult, i’m a newbie to DVWP. Thanks, Lee
Dear Laura, thanks for the great video. I have the similar remark like Stacy. Not sure, if you have answered that question. As shown in the video the last column are calculated by “Travel div Emp”. I would like to sum up the values of that column as the calculated value in the last row “sum(Travel) div sum(Emp)” is not the sum of the column. Thanks
Hi Laura, Thanks so much for the screencast. I am having an issue though and after some lengthy testing, it appears that if any amount in a list is over 1000, the Sum calculation doesn’t work and returns “NaN” as the result. If I make all items in the list under 1000 it works fine and I get the correct sum. (NB. Also noticed that if the column is created as Currency, and not Number, the Sum is returned as “NaN” also) I had found some posts suggesting adding a “FIXED” function to the equation but I am a little simple when it comes to coding so have no idea how to add it to I would love some help if you know how to do it 🙂
Stacy, Is yours a data view web part or a 2010 list view web part? If it’s a LVWP, in SPD 2010, click the Design tab in the ribbon, and then check the box next to Totals Row. Diff between the 2 view types: http://www.youtube.com/watch?v=r2eODYHp73A Kelly, Sorry, I’ve never seen that happen. In general I don’t know much about XSLT, and I usually have to figure it out from scratch whenever I have to do it.
The reference to adding FIXED is crucial if you will be summing totals above 999. Once that comma gets in the calculated filed, the system sees it as a string while summing, thus the NaN. The FIXED fix simply means wrapping your forula int he calculated field you created in your list settings with the FIXED Excel function, like FIXED(Days*Rate).
I know this is an older post, but so far your webcast has gotten me the closest, but still no complete solution. I have a calculated column that is the product of Units * CostPerUnit and I am looking to sum them as a nodeset, but nothing seems to work. I have tried sum($nodeset/@CostPerUnit*@Units) & with $Rows. Any thoughts?
Sean,
One trick that I had to do recently was to use the column with the the . dot in front of it. So, it would be .@CostPerUnit*.@Units
Try that?
Hi! any chance the video you reference is on Youtube? I am unable to access the video while at work – the online storage area is blocked.
Sorry Ben, no it isn’t.
Hi Laura, is there anyway you can post this video on youtube? I am unable to view the video and I really need this solution. You are the only one that figured out how to do it. Please?
Laura hello and excellent materials provided, can you let me know if you have the sum for calculated columns but for SharePoint 2013?
thank you
can you let me know if you have the sum for calculated columns but for SharePoint 2013 without share point designer and xsl code
I don’t, no.
Laura, this is great, but in my case I am doing a unit cost x qty calculation to get an extended cost per row. Doing the match at the totals level produces the wrong result (sum of Qty x sum of Unit Cost), what I need is sum(Qty * Unit Cost). This produces nodeset return errors. Likewise, I am unable to merely sum the formula column (which is what I really need).
Thoughts?
After much futzing and deciding I don’t wish to deep dive into xsl to learn how to create a nodeset that I might them sum, I solved this another way… I created a currency field and use a quick Nintex workflow to populate the extended cost calculation to that field on create or change. Problem solved (in near, but not quite real time).