User Defined Function to sum all excluding strikethrough

Question:

Is it possible with Google Spreadsheet to calculate the fields (currency) and ignore all fields that have had a strikethrough applied?

Solution:

Following is the function that you need to put in to the script of the spreadsheet:

function SumIfNotStrikeThrough(rangeA1Notation)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var mysheet = ss.getActiveSheet();

  var dataRange = mysheet.getDataRange();
  var mydatarange = mysheet.getRange(rangeA1Notation);
  var numRows = mydatarange.getLastRow();
  var rowindex = mydatarange.getRowIndex();
  var columnindex = mydatarange.getColumnIndex();
  
  var total =0;
  
  for(i=rowindex;i<=numRows;i++)
  {
    if(dataRange.offset(i-1, columnindex-1, 1, 1).isBlank() != true && dataRange.offset(i-1, columnindex-1, 1, 1).getFontLine() != "line-through")
    {
      var temp = dataRange.offset(i-1, columnindex-1, 1, 1).getValue();
      total = total + temp;
    }    
  }
  
  return total;
}

Now, if you want to call this function then you can directly call it in any cell, for example in Cell A8:

=SumIfNotStrikeThrough("A1:A5")

This will give you the total of the range A1:A5 but it will not count the values which are strikethrough.

Look at the following screenshot:

If you are new to Google Spreadsheet Scripts, then please have a look at 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.

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