SUMIF not strikethrough

Question

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

Solution 1

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.

Solution 2

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

Comments

[Henrique G. Abreu]

By the way, that's an ugly implementation. First of all, because you missed the caching feature/bug, second you're using unnecessary API calls within a for-loop, which is inefficient 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.

[Jeremiah Smith]

@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 (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).

[chAlexey]

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;

[Mahsa2]

An example for SUMIF with A checkbox and E data: =SUMIF(A1:A100, true, E1:E100)