Wednesday, September 12, 2012

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:



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,


30 comments:

  1. Thanks so much for this, kind of works but has a small issue that it gets messed up when there are empty fields in the column being checked.

    is that something that can be fixed?

    ReplyDelete
    Replies
    1. Hi cybercampbell,

      I have checked the condition of whether the cell is empty or not and then I have calculated the total sum.

      This script is working fine even if some cells are blank, still if you have any issue then you can share a copy of your spreadsheet with me and I will look into it & try to provide you a solution.

      Thanks,
      Kishan.

      Delete
  2. Hi Kishan

    I can't chare my document as it's not my data to share. Could you please attach your working version here?

    Thanks
    C

    ReplyDelete
    Replies
    1. Check out this spreadsheet:

      https://docs.google.com/spreadsheet/ccc?key=0AmMTqpzD9YRndGRZQWhZQ3JicmpBOVd3cmhNUi1iY1E#gid=0

      Delete
    2. need permission to view the file

      Delete
    3. I have granted you the permission... Check out the spreadsheet now...

      Delete
  3. Sorry you're right, it does work.

    I got confused that there were functions in my new script that were there be default when I created it so I left them in. Once I removed them to just have yours everything worked fine.

    One thing though.. if I update the rows it doesn't recalculate. Is there a way to add a trigger to the menu like in the google example. looks like addmenu is the function.

    ReplyDelete
  4. It doesn't recalculate when an update is made :(

    ReplyDelete
  5. I'm getting an error: error: Argument must be a range (line 7, file "Code"). How do i fix this please?

    ReplyDelete
    Replies
    1. You can share a copy of your spreadsheet, and I will look into it & try to provide you a solution.

      Delete
  6. Hello

    I have never done anything with scripting before ... and I seem to do something wrong.
    So I added the function of sumifnotstrikethrough via the scripteditor to this particular spreadsheet.

    At first it seems to work, all values are calculated well, but then when I strikethrough or add/delete a value the spreadsheet does not get updated ... could you please help me with that ... thanks

    This is how the script looks now:

    /**
    * Retrieves all the rows in the active spreadsheet that contain data and logs the
    * values for each row.
    * For more information on using the Spreadsheet API, see
    * https://developers.google.com/apps-script/service_spreadsheet
    */
    function readRows() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var values = rows.getValues();

    for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
    }
    };

    /**
    * Adds a custom menu to the active spreadsheet, containing a single menu item
    * for invoking the readRows() function specified above.
    * The onOpen() function, when defined, is automatically invoked whenever the
    * spreadsheet is opened.
    * For more information on using the Spreadsheet API, see
    * https://developers.google.com/apps-script/service_spreadsheet
    */
    function onOpen() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var entries = [{
    name : "Read Data",
    functionName : "readRows"
    }];
    sheet.addMenu("Script Center Menu", entries);
    };

    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;
    }

    ReplyDelete
  7. i always get this error
    Argument must be a range (line 7, file "Code")

    ReplyDelete
  8. Hi Kirshan, thanks a lot. Just wondering... when I entered formula shown above (=SumIfNotStrikeThrough("A1:A5")), it does not work... I did strike through the font manually if it makes any difference... guessing not but saying just in case...

    ReplyDelete
  9. Anyone fix the error issue? I too am getting an error, odd readout and not auto-recalculating.

    ReplyDelete
  10. Remember the range must be a string, for example "A1:A5", otherwise you'll get "Argument must be a range (line 7, file "Code")" error.

    ReplyDelete
  11. It doesn't seem to update the value if an edit is made to the spreadsheet (once set). Does anyone know a solution to this?

    ReplyDelete
  12. For anyone experiencing the Error mentioned by Marty and Tania above (or one like it), I solved it by replacing (rangeA1Notation) on line 7 with ("E5:E"), the quotes are necessary, and it will no longer produce and error.

    However, as many have mentioned, it still does not recalculate on any trigger (on open, on change, on edit, on form submit, time-driven, etc.)--I have tried them all.

    Any ideas?

    ReplyDelete
  13. I also have the issue that the field doesn't update if I add more strikes to the columns

    ReplyDelete
  14. First off, thank you very much for posting this script!

    I am having an error when running the function. It is giving me a date as the answer rather than the sum of the digits in the range. I do have some blank sums in the range. Any idea on how to fix this?

    THANKS!

    ReplyDelete
  15. This is a fantastic solution, but it stops being useful if the SUM doesn't refresh with new values. Anyone found a fix for this?

    ReplyDelete
  16. if I change the "!= "line-through")" to "= "line-through")" I get an error. Is that expected?

    ReplyDelete
  17. To make the total update when you change values, just pass the range again without brackets:

    =SumIfNotStrikeThrough("B11:B17", B11:B17)

    ReplyDelete
  18. To make the total update when you change values, just pass the range again without brackets:

    =SumIfNotStrikeThrough("B11:B17", B11:B17)

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete

  20. This does not work for me.
    The total updates when I input new figures into the range, but the total does not subtract figures with strike-through formatting.
    Any clues as to why this is may be happening?

    ReplyDelete
  21. Is there a way to copy the formula to the adjacent cells via Dragging while the selected cells change based on that?
    for example,
    =SumIfNotStrikeThrough("B4:H4",B4:H4)
    Drag to Next row & it changes to
    =SumIfNotStrikeThrough("B5:H5",B5:H5)

    ReplyDelete
    Replies
    1. is there a simple way to copy the formula to multiple rows/columns?

      Delete
  22. This doesn't work for me, it just returns the sum back as '0'.

    ReplyDelete