Google Spreadsheet Script to calculate sum from Column values

Question:

I'm using Vista

My question is the following:

I have a spreadsheet that is shared and will be progressively populated. Every fifth row of a given column will be getting a number, and I want to calculate the total of that. Other elements of the same column will contain other numbers that I don't want to use. 

Right now, I am summing elements of column C. Specifically C5+C10+C15. So far I have put this down by hand, each week, many people will be adding content that will be falling in C20, C25, etc... In principle, the last cell can be many rows down, say larger than 1000.

What I need is to have one cell that keeps the total sum. Something like a C for loop like this

sum=0

for(i=5;i<=1000;i=i+5)

{sum=sum+c[i];}

Can I do this in a simple way?

Thanks,

Ed

Solution:

Here is the script to get the sum as per the condition mentioned in the above question.

Now, open script editor and paste the following script in it:

function onOpen() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var entries = [{name:"Calculate Sum",functionName:"CalculateSum"}];

  sheet.addMenu("Calculate", entries);

};

function CalculateSum() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = ss.getSheetByName("Sheet1");

  var dataRange = s.getDataRange();

  var lastrow = dataRange.getLastRow();

  

  var values = s.getRange(1,3,lastrow,1).getValues();

  var sum=0;

  

  for (var i=5;i<=lastrow;i=i+5)

  {

    sum=sum+values[i-1][0];

  }

  Browser.msgBox(sum);

};

The above script will calculate the sum from Column C of 'Sheet1' and you can change it to as per your requirement.


And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive