1

I have a requirement that I need to sum a column in my SharePoint page then send a email if its one or bigger. I have got the email part down and its working, the problem is everything that I can fined only sums rows. Is there a way to do this with a calculated field, a workflow in SharePoint Designer, or a calculated column?

  • Very sadly... SharePoint cannot sum a column without writing code. – mfoy_ Jun 17 '15 at 17:12
0

In 2013 its best to apply CSR with a JSLink, but this method has some drawbacks like having to set the JSLink for every View, and not being able to (easily) have multiple Views on one page.

In SP2010 I used these steps to Sum a Calculated Column (which can not be done with a View Total)


(source: viewmaster365.com)

It uses Javascript in a Calculated Column to do the math while the page is loading.

http://www.viewmaster365.com/#/Create/Sum shows how to built the Formula step by step. The complete Formula is:
(remember to set the datatype to Number)

="<div style=""color:"
 & IF(    [Rate]<[Buy]   ,"red","green")
 & """>"
 & DOLLAR(   [Qty]*[Rate]   )
 & "</div>"
 & "<div class=""vmSums"" style=""display:none;font-weight:bold;border-top:1px solid black;""></div>"
 & "<img src=""/_layouts/images/blank.gif"" onload=""{"
     & "var TBODY=this.parentNode.parentNode.parentNode.parentNode"
     & ",dataSum='data-vmSum',dataCnt='data-vmCount',total=value="
 &    [Qty]*[Rate]   
     & ",count=0;"
     & "if(TBODY.getAttribute(dataSum)){"
         & "total=parseFloat(TBODY.getAttribute(dataSum))+value;"
         & "count=~~TBODY.getAttribute(dataCnt)+1}"
     & "TBODY.setAttribute(dataSum,Number(Math.round(total+'e2')+'e-2').toFixed(2));"
     & "TBODY.setAttribute(dataCnt,count);"
     & "window.clearTimeout(window.vmSumFunc);"
     & "window.vmSumFunc=window.setTimeout((function(){"
         & "var lastrownr=~~TBODY.getAttribute(dataCnt),"
         & "last=document.getElementsByClassName('vmSums').item(lastrownr),"
         & "total=parseFloat(TBODY.getAttribute(dataSum)).toFixed(2);"
         & "last.textContent='$'+total;"
         & "last.style.display='block';"
     & "}), 100)"
 & "}"">"

Some things to note:

  • Calculated Columns set to datatype:Number can output HTML (since SP2010)
  • Using the SCRIPT tag was removed by Microsoft in summer 2013
  • but loading a blank IMG can still trigger Javascript in the onload function
  • This is all executed while the page is loading (so SP.js is not available and everything down the HTML page is not available yet)
  • Javascript Timeout (canceled by every next IMG onload) trick is used to execute the final Display of the Total once.

CalcMaster Bookmarklet to edit Formulas

It is a PITA to debug Calculated Columns. Because you don't get feedback until you save a Formula and you end up having to click multiple times to get back to your Formula.

I have written a small 'CalcMaster' bookmarklet which hooks into the formula-editor and does a save of the Formula on every keypress; giving immediate feedback.
Recently published a first version on GitHub:

https://github.com/Danny-Engelman/CalcMaster

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.