7

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

Community
  • 1
  • 1
Cybercampbell
  • 2,358
  • 10
  • 44
  • 75

2 Answers2

7

Cell formatting can not be used as a criteria on spreadsheet formulas as there's no formulas (AFAIK) that can grab a cell formatting.

It would be much easier if you'd have another column where you marked your values with a cell value change, like 'yes/no' or an 'x'.

You can do it in Apps Script. But relying in formatting to make calculations in Apps Script is tricky. I recommend have a menu triggered function that grabs the "strikethrough" format and generate a 'yes/no' column so you can use in regular formulas like SumIf.

Community
  • 1
  • 1
Henrique G. Abreu
  • 15,954
  • 2
  • 51
  • 63
3

Answer is here: http://igoogledrive.blogspot.ca/2012/09/user-defined-function-to-sum-all.html

Cybercampbell
  • 2,358
  • 10
  • 44
  • 75
  • 2
    By the way, that's an ugly implementation. First of all, because you missed the [caching feature/bug](http://stackoverflow.com/questions/9022984/google-apps-script-to-summarise-data-not-updating/9023954#9023954), second you're using unnecessary API calls within a for-loop, which [is inefficient](https://developers.google.com/apps-script/best_practices) and may fail for longer ranges. Lastly, you should have pasted your code here in StackOverflow, it's ok to link to your blog, but forcing us to check it is not nice. – Henrique G. Abreu Apr 20 '14 at 00:48
  • @Cybercampbell Is there a chance you could publish this script as an add-on for google sheets? Having an actual add-on allows for real-time function calling inside a cell, making real-time value update possible (https://developers.google.com/apps-script/guides/sheets/functions). It would really help with household budgeting, as you get the accounting side with it (i.e. to see what's still going out of your bank account, as banks take ages to update, causing misspending). – Jeremiah Smith Feb 17 '17 at 01:40
  • Thanks for this piece of code! That helped me a lot! If anyone else would need kind of "CountIfNotStrikeThrough" function, you only need to replace two line: instead of `var temp = dataRange.offset(i-1, columnindex-1, 1, 1).getValue(); total = total + temp;` do the following `total = total + 1;` – chAlexey Jan 21 '19 at 11:40