JQuery for Everyone: Total Calculated Columns

Problem: SharePoint will not perform “Totals” (Settings > Edit View) on a Calculated Column.

Solution: Use jQuery to create an array, do the math with JavaScript, and insert the value in the Totals row.

To use this script, edit your view to use “Totals” on at least one column in your list. Edit the view’s page and add a Content Editor Web Part (CEWP). Insert the following script in the Source Editor adjusting the col variable as needed (col = 4 above).

Update: Therese commented on this article and had a great idea–make it work with groups. I did some research and came up with a single snippet that does it all with no script configuration.

With the new script, configure your list to display your calculated column that needs totaling last. I did this mainly because grouped rows have an additional, hidden column for the exact same list and I didn’t want to update the script between views.

One note on a view with groups: you will not have an “overall total” because the data does not exist on the page until the group opens. This would cause the “overall total” to only equal the total of visible groups which does not match how the other Total functions work.

(UPDATED!).

Original recipe:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script>
<script type="text/javascript">
function addCommas(nStr) {//formats number
	nStr += '';
	x = nStr.split('.');
	x1 = x[0];
	x2 = x.length > 1 ? '.' + x[1] : '';
	var rgx = /(\d+)(\d{3})/;
	while (rgx.test(x1)) {
		x1 = x1.replace(rgx, '$1' + ',' + '$2');
	}
	return x1 + x2;
}
$(function() {//sums money in specific list column
	var col = 4; //which column to sum
	var m = "$"; //change to "" for non-money format
	var arrayList = $("table.ms-listviewtable:first> tbody:eq(2)> tr").find(">td:eq("+col+")").get();
	var x = 0;
	var p1 = "";
	var p2 = "";
	$.each(arrayList, function(){
		x += Number($(this).text().replace(/\$|,|\)/g, "").replace(/\(/g,"-"));
	});
	if (x < 0) {//format for negative numbers
		p1 = "(";
		p2 = ")";
		x = Math.abs(x);
	}
	$("#aggr> tr:first> td:eq("+col+")")
		.css("text-align","right")
		.html("<b>Total = "+p1+m+addCommas(x.toFixed(2))+p2+"</b>");
});
</script>

New, with grouping support:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script>
<script type="text/javascript">
function addCommas(nStr) {//formats number
	nStr += '';
	x = nStr.split('.');
	x1 = x[0];
	x2 = x.length > 1 ? '.' + x[1] : '';
	var rgx = /(\d+)(\d{3})/;
	while (rgx.test(x1)) {
		x1 = x1.replace(rgx, '$1' + ',' + '$2');
	}
	return x1 + x2;
}
function CalcGroup(groupName) {//sums money in specific list column
	var x = 0;
	var m = "";
	var p1 = "";
	var p2 = "";
	if (groupName == '') {
		$("table.ms-listviewtable:first> tbody:eq(2)").attr("id","tbod");
	}
	var arrayList = $("#tbod"+groupName+"> tr")
					.find(">td:last").get(); //array for groups
	$.each(arrayList, function(i,e){
		x += Number($(e).text().replace(/\$|,|\)/g, "").replace(/\(/g,"-"));
		if ($(e).text().indexOf("$") >= 0) {	m = "$" };
	});
	if (x < 0) {//format for negative numbers
		p1 = "(";
		p2 = ")";
		x = Math.abs(x);
	}
	if (arrayList.length > 0) {
		$("#aggr"+groupName+"> tr:first> td:last")
			.css("text-align","right")
			.html("<b>Total = "+p1+m+addCommas(x.toFixed(2))+p2+"</b>");
	}
}
//rewrite of WSS function
function ExpGroupRenderData(htmlToRender, groupName, isLoaded)
{
	var tbody=document.getElementById("tbod"+groupName+"_");
	var wrapDiv=document.createElement("DIV");
	wrapDiv.innerHTML="<TABLE><TBODY id=\"tbod"+groupName+"_\" isLoaded=\""+isLoaded+"\">"+htmlToRender+"</TBODY></TABLE>";
	tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
	if (CalcGroup) {
		CalcGroup(groupName+"_");
	}
}
$(function() {
	CalcGroup('');
});
</script>

Paul Grenier

View all entries in this series: PaulGrenier-JQuery for Everyone»

Entries in this series:

I recommend SharePoint resources daily on my twitter account here.

Posted by AutoSponge on Tuesday, January 6, 2009 at 9:00 am
Filed under Tips and Tricks · Tagged with

Please Join the Discussion

79 Responses to “JQuery for Everyone: Total Calculated Columns”

Charlie Epes says:

January 6, 2009 at 10:14 am

Paul:
This is tremendous!!!! Thank you so much for working on this.

Charlie
Shaun says:

January 6, 2009 at 10:30 am

This really is awesome. I spent alot of time struggling with the Totals in Calculated Columns Issue. This will be extremely useful in the future. Thanks!
eric says:

January 6, 2009 at 11:51 am

Will this work for time values? I can see this useful for calculating outage times but the default calculated time values are tough to work with.
AutoSponge says:

January 6, 2009 at 1:37 pm

@Eric,

A version of this will work for time differences. Part of that solution requires a well-formatted calculated column.

~Paul
Therese says:

January 6, 2009 at 3:20 pm

Hi, Thanks for this great script!
It worked well but on some of my views I have groupings and it won’t calculate.
Is there a small modification you can suggest that would help with this?
Also if there could be totals on the groupings?
Thanks!
AutoSponge says:

January 6, 2009 at 5:01 pm

@Therese,

The selector part for groups is easy but the page loads in stages when there are groups, sort of like the dynamic loading done with the EWA (a few articles ago).

Running with the new selector on document.ready = $0 but running after the groups completely load works fine.

So, it will take an afternoon or so to figure out how I can tell when the groups have finished loading. But since the EWA article, I’m much better at identifying this issue and I have a better strategy for finding my “hook.”

This technique will be pt10 of my JavaScript series… it’s too useful.
AutoSponge says:

January 6, 2009 at 9:31 pm

I updated this post instead of making another one. Thanks for the idea.
L Larson says:

January 9, 2009 at 9:23 am

It might be very good for people to put together your program with the programs of Allan Jardine.
Just do a google search with the terms: “Allan Jardine” tables
AutoSponge says:

January 10, 2009 at 12:04 pm

I don’t think I can hold a candle to Mr. Jardine’s work but I can certainly learn a lot from the site. Thanks for the link!

~Paul
Patrick Colbeck says:

January 21, 2009 at 8:12 am

Excellent information! It also potentially solves a few other problems that I have requiring calculation loops!

Another way of getting the columns to total is by using workflows. The basic steps are as follows:
-Create list with calculated column
-Add a placeholder column in number format
-Create a workflow that updates the placeholder column with the value in the calculated column upon creating a new item or changing the item

You can use data view web part grouping features to perform calculation rollups as well.

NOTE: It also solves the problem of calculated columns not showing in data view web parts.
Henrik Andersson says:

January 21, 2009 at 10:22 am

Hi,

Regarding the loading of data when expanding a group. I have done some javascript code that hooks a method to all expand/collapse icons, so when a users expands (onclick for element img) a group I can run some code. I can not share this, but that is how I have solved the issue.

// Henrik
AutoSponge says:

January 21, 2009 at 5:46 pm

@Henrik,

Thanks for the tip, I’m still learning; and methods–thus far–have escaped my attention. I’ll be sure to look into it.

~Paul
Henrik Andersson says:

January 30, 2009 at 5:41 am

Hi,

I have made some changes in the script so it can be used for any column,
http://henrikfromsweden.blogspot.com/2009/01/response-to-jquery-for-everyone-total.html
larry says:

February 12, 2009 at 3:51 pm

ditto to all the posted comments, great job. One quick question. can this total display the totals of the grouped items also? i inserted the grouped script, but if the group is not expanded you can not view the total. By default the sum will sum at each level of the group. Can this manage the same functionality? can we total or sum the sum? each level we drill down the totals are visible, can we grab that data?
AutoSponge says:

February 12, 2009 at 4:04 pm

@larry

because it’s javascript, we’re only looking at the DOM. Non-expanded groups in the normal List View have not loaded yet, and are not part of the DOM.

Some DVWPs will load all of their groups when the page loads (even non-expanded groups) so you might see a grand total there.
Kevin says:

February 17, 2009 at 6:13 pm

I’ve tried the script, but I’m getting a Total = Nan value at where grand totals are valued and nothing at the group level. Any help?
AutoSponge says:

February 17, 2009 at 7:11 pm

NaN stands for ‘not a number’ and it usually means the column targeted was the wrong one.

Make sure you set:
```
var col = 4;
```
To the correct column for your list. The count starts at zero (not one) but you have to count the attachments column if you have that in your view (see image above).
William Piersol says:

March 2, 2009 at 5:43 pm

First off, great job on this work-around!

Is it possible to used this on multiple list in the same page? I tried it on with a page with 3 web part zones. I installed a list in each, and a Content Editor in each. The first works great, but the second and third not so much. I assume I need to tell it which list..

Any idea?

Thanks,

WOPiersol
AutoSponge says:

March 2, 2009 at 10:58 pm

@William,

This script was made to work with just one instance per page (table.ms-listviewtable:first). It could be modified to work with other web parts–if you know a little JavaScript ;)
Thomas says:

March 3, 2009 at 9:17 am

I’ve got the same problem as Kevin described above: I am getting a NaN error when the number has more than six digits.
I am using a calculated column with a currency value in Euro instead of Dollar.
That aside the biggest problem is that the returned value is not correct because of the european punctuation: Period should be comma and comma should be period.
For an example: $123,456.78 –> 123.456,78 €
When i enter $123.456,78 as a value without making any calculation the result is $123.46.
I tried to change the split operation but that didn’t work.

Is there a chance to make this code work under an european version of SharePoint? That would be great! Especially with the € currency.
I tried everything i can to adapt this code but nothing did work.
thewire says:

April 14, 2009 at 8:16 am

I am using the updated script to support grouping, but I get Total = NaN for my column. If I remove grouping then the total shows just fine.
Ken says:

April 17, 2009 at 2:44 pm

This jquery works great but I have a problem. I have created a Webpart page with a parts list in a view grouped and set for totals. I have also added a form webpart to filter the contents by work order. In the list webpart, when I click the + sign I see the totals, but if I type the work order number in the form webpart when it shows the workorder, I click the + sign and there is no totals. Any way to make that show totals?
AutoSponge says:

April 18, 2009 at 6:45 pm

@Ken,

The problem, I suspect, is that when you filter the web part, you are hiding rows. You end up hiding the row holding the total.

This means you will have to place the total row somewhere else or use a different method of filtering the list.
Ken says:

April 20, 2009 at 6:33 am

I may not be making myself clear. When I look at the web part on the page that is grouped by work order number, I can click the plus sign and the totals show. It is only when I use the form web part and pass the T1 to the work order number that it doesn’t work. When it pulls up the single order and I click the plus sign, the totals are not there. It’s as though when it is filtered by the form web part, that it creates an different view that isn’t using the jquery. It may not be possible to do this. Thanks!!!
Amanda says:

May 13, 2009 at 3:32 pm

Hi there. I added the script into a CEWB. Any thoughts on how to AVERAGE a calculated column, not total it? I am using the list with groups version. What I did works, I can see the calculated column, but it’s totaling the numbers. I need the average.

Thanks!
AutoSponge says:

May 13, 2009 at 6:56 pm

Amanda,

I’m now using a call directly to OWS to get the data and I’ve written a plugin that has all of the calculation methods built in:

http://www.endusersharepoint.com/?p=1611

However, if you want to try modifying this script, divide the sum by the array’s length to get an average. Just keep in mind that it can’t calculate what it can’t see (unlike the OWS call).
Amanda says:

May 15, 2009 at 1:30 pm

Thanks AutoSponge. Question on that. My list doesn’t have a set number that I can divide the sum by. Essentially, I want to be able to see an average of my calculated column across varying group totals. For instance, if we use WalMart and WalMart is in 8 cities, how is WalMart’s average profit across those 8 cities. Then, we have KMart in 10 cities. How is KMart doing across those 10 cities.

So you see, I don’t have a set number to divide by because 1 store could be in 8 cities and another in 10.

Thoughts?
Richard says:

May 19, 2009 at 10:17 am

I’m also interested in averaging a calculated column without a set length.
AutoSponge says:

May 19, 2009 at 5:40 pm

Amanda and Richard,

Unless I’m missing something, I think you just need to use the second script, with grouping support, and group by “Store” so Walmart only totals Walmart records.

To get an average, you divide the Sum by arrayList.length. So, before this line:
```
	if (x < 0) {//format for negative numbers
```
add:

x = x/arrayList.length;

The rest of the script should complete as normal.
Melissa says:

May 22, 2009 at 7:32 am

Thank you so much! That has to be the easiest solution I’ve ever found working with Sharepoint!
sam says:

July 6, 2009 at 10:35 am

fabulous article! but I tried in all methods you have mentioned, I am getting error Total = NaN.
Basically What I am trying to do:
I have two calculated avg columns in a list group by Region. I am looking the total averages which is group by Regions!
I used your 2nd code snipped and x = x/arrayList.length;
Please advice!!!
patric says:

July 7, 2009 at 4:01 pm

Please ignore the above msg! I sort it out!
but just wondering to know
1)how to get total of the group?
ex: I can see each branches total but cannot able to see the consolidated result in regions and countries!
2) I have 5 columns in display view where 2 of them are integers which I want to show the total averages? I can work it out for only one column the other never display!
please advice!!!
AutoSponge says:

July 7, 2009 at 6:21 pm

@Patric,

If you like this, check out my work on owsapi.codeplex.com, that has a better way to work with list data.

If you’re trying to do this with two columns, I think the above code will need heavy modification. Better to use the owsapi.
Robert says:

July 15, 2009 at 3:20 pm

Hi Paul,
Would it be possible to show the Sum, Avg, Total at a group level (overall consolidated result)
Thank you!
AutoSponge says:

July 16, 2009 at 8:26 am

@Robert,

I’m not entirely sure what you mean. There are two scripts above, one works with groups. However, my preferred method these days is owsapi (http://owsapi.codeplex.com)
Chuka Arinze says:

July 17, 2009 at 10:23 am

DO you have the script to get the Average of Calculated Columns?

Thanks and best regards
Arlo says:

July 21, 2009 at 9:48 am

Thanks for this great post. I’m trying to calculate an Average like Amanda and Richard were; however, when I use the “x = x/arrayList.length;” line in the grouping script, the value returned is not accurate. The value of the calculated average it displays is smaller than the value of any of the individual items, so it appears that the value of arrayList.length is too large by several items (i.e. dividing the sum by 12 when there are only 5 items in the array.
Simon Hudson says:

July 26, 2009 at 4:44 am

I also got the NaN error at first. I figured that all the examples use US dollars and reviewing teh code I spotted that the currency symbol is coded into the expression parser (and don’t even get me started on the prevalent US assumption that it is the only country on the planet that uses computers).

Changing the line to replace the second $ with the currency symbol you use should get the right total to show up, though I haven’t quite worked ouut how to put a suitable currency symbol back in:

x += Number($(e).text().replace(/$|,|\)/g, “”).replace(/\(/g,”-”));

It would be even better if the code looked at the number format, extracted the symbol and replaced it automatically rathher than havinng to alter the code according to the currency, but that’s beyond my skill level.
Simon Hudson says:

July 26, 2009 at 4:53 am

Addendum to the above, you should also replace the second and thirds $ symbols in this line (replaced to £ here):
if ($(e).text().indexOf(”£”) >= 0) { m = “£” };

Now, if someone can advsie how to set the number of decimal places (I want none)…
AutoSponge says:

July 26, 2009 at 5:13 pm

@Simon,

The JavaScript method for controlling precision is toFixed. Google that and you should be on your way.

Check your arrays (use console.log or send them to global variables you can check from the console). If you’re getting the wrong column’s values, I have some new techniques for this using cellIndex I can share.
Ken says:

July 27, 2009 at 11:36 am

Any way to perform a similar action but add up a total of hours and minutes?
AutoSponge says:

July 28, 2009 at 7:20 am

@Ken,

Check out the next article in the series: http://www.endusersharepoint.com/?p=1134
Chris says:

August 6, 2009 at 12:46 pm

This worked great!
Thank You
Ken says:

August 6, 2009 at 5:53 pm

Works perfectly, thanks so much
Emilie-Anna says:

October 20, 2009 at 11:25 am

Hello There, Thank you for Sharing your code but it doesn’t work to me even if i followed the steps:

-created a view with a total on a column
-added the code to CEWP of the correspondant view
-using the code with the groups in it(without modifying it)
Result : having a javascript error Object Expected on the line corresponding to the
function addCommas(nStr) {//formats number
nStr += ”;
x = nStr.split(’.');
x1 = x[0]; // here
x2 = x.length > 1 ? ‘.’ + x[1] : ”; //here
var rgx = /(\d+)(\d{3})/; //here
while (rgx.test(x1)) { // here
x1 = x1.replace(rgx, ‘$1′ + ‘,’ + ‘$2′);
}
return x1 + x2;
}

is it normal? Can you Help me Please?
Regards
Emilie-Anna
AutoSponge says:

October 21, 2009 at 7:16 am

@Emilie-Anna,

You’re missing at least two steps. First, you must include a Total row using one of the built-in functions from your view (on a column other than your calculated column).

Set the col variable to the column number you need totaled.

Re-read the instructions carefully. This solution was not as easy to implement as most of the others.
Marco says:

October 22, 2009 at 11:07 am

I have same problem of Thomas:

the returned value is not correct because of the european punctuation: Period should be comma and comma should be period.
For an example: $123,456.78 –> 123.456,78 €
When i enter $123.456,78 as a value without making any calculation the result is $123.46.

Please help me with this problem and thanks for your very appreciated work!!!
AutoSponge says:

October 23, 2009 at 8:53 am

@Marco,

An international version would essentially be a total rewrite. Although the basic elements are there, we’d need to rearrange the order and punctuation to deal with a comma that needs to be a period for calculation.

If I get to it, I’ll post here that a new version is up.
Marco says:

October 23, 2009 at 11:10 am

Workaround for the problem!!!

replace this line:

x += Number($(this).text().replace(/\$|,|\)/g, “”).replace(/\(/g,”-”));});

with this:

x += Number($(this).text().replace(”.”, “”).replace(”,”,”.”).replace(/\(/g,”-”));

Work for me… :)
Terry says:

October 27, 2009 at 2:14 pm

I am seeing this for the first time today because I have a need for it. I know nothing about code at all. Can anyone tell me what the items are in the second example I need to edit to make this work?

Thanks!

Trackbacks

Check out what others are saying about this post...

JQuery for Everyone: Total of Time Differences | End User SharePoint says:

January 6, 2009 at 2:24 pm

[...] Eric commented on my last article and [basically] issued another challenge. Will this work for time [...]
JQuery for Everyone: Get XML List Data with OWSSVR.DLL (RPC) | End User SharePoint says:

April 21, 2009 at 5:00 am

[...] Anyone developing solutions around List View web parts may find this helpful. A common request from users involves performing various math functions on calculated columns. OOB, SP does not do this which lead me to develop code like Total Calculated Columns. [...]
The Bamboo Team Blog says:

April 24, 2009 at 3:17 pm

How to Total Calculated Columns in a SharePoint List…

A customer recently approached us with the following problem: How can one Total (sum, average, Max, Min…
Jquery and Sharepoint for Totals and Averages for List Views | The Sydney Sharepoint Consultants, Developers and Trainers says:

April 29, 2009 at 12:18 am

[...] column” which normally presents Sharepoint a problem. Luckily i came across this this post to help [...]
JQuery for Everyone: Extending OWS API for Calculated Columns | End User SharePoint says:

May 6, 2009 at 4:09 pm

[...] column with groups on the page with the groups closed. SharePoint does not do this OOB and my previous attempt at a solution needed data rendered (groups [...]
EndUserSharePoint.com: Top 10 Commented Posts | End User SharePoint says:

August 19, 2009 at 4:00 am

[...] JQuery for Everyone: Total Calculated Columns (49) [...]