Script: SUM values if font is bold – Google Drive Spreadsheet
I was working on some calculations using Google Drive Spreadsheet.
Than I found out, that I would need SUM values only from cells, which has bold font… Than I found out, that there is no simple way, how to do that with basic functions included in Spreadsheet application. That was the moment, when I found out, that there is option create a custom fuction by my own needs using Google Apps Script!
I checked existing gallery of the scripts to find out a syntax of the Google Apps Script. Than I checked their amazing documentation and wrote my own script. Here is its code:
function sumWhereIsFontWeight(rangeSpecification) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getRange(rangeSpecification); var x = 0; for (var i = 1; i <= range.getNumRows(); i++) { for (var j = 1; j <= range.getNumColumns(); j++) { var cell = range.getCell(i, j); if(cell.getFontWeight() == 'bold') x += parseFloat(cell.getValue()); } } return x; }
I also prepared function for the opposite situation, and called it sumWhereIsNotFontWeight, here is its code:
function sumWhereIsNotFontWeight(rangeSpecification) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var range = sheet.getRange(rangeSpecification); var x = 0; for (var i = 1; i <= range.getNumRows(); i++) { for (var j = 1; j <= range.getNumColumns(); j++) { var cell = range.getCell(i, j); if(cell.getFontWeight() != 'bold') x += parseFloat(cell.getValue()); } } return x; }
UPDATE (2013/11/15):
I have created new article where I explain more in detail, how to implement this script into Google Drive Spreadsheet, check it here.
I copied and pasted your code sumWhereIsFontWeight exactly as you have it. When passing a range to it, it gives an error
=sumWhereIsFontWeight(A720:B723)
Error: Argument must be a range line 4
You are missing quotation marks inside brackets. It should be like this:
=sumWhereIsFontWeight(“A720:B723”)
Please note, that wordpress converted those quotation marks im my comment somehow (like for quote), so use regular ones …
hi.
where did i mistake .. because when i copied all rows without number of rows and put it like it is in to the box, specify range as post up.. it is placing script as text. when i put on the start of the box = i will get ERROR – Parse error.
please do some print screen straight tin Google spreadsheet..
thank you
BR
rasto.
Hi Rasto,
You are not supposted to insert this script into spreadsheet column, but You have to add this sript at Scripts editor.
I just created a new article, where I explain it in detail, so check Homepage of my blog 😉
Cheers
Lukas
I have been trying to get this to work, and now it seems that it does… However the value does not update on its own. The only way it seems to update is if I go back in and change the range, hit enter, go back and change the range back, and hit enter again. Does that make sense? Is this a problem with Google, or perhaps my computer is too slow? Thanks!
Hi Eric,
I will check it out – it was working fine for me. If I will find out something, I will post updated post with described modification – it is possible, that Google changes something since I have published this guide.
This website was… how do I say it? Relevant!! Finally I’ve found
something that helped me. Cheers!
I’m encountering the same problem that Sepero did. After copying and pasting these values into the script editor I click “Run” and it responds with “Argument must be a range line 4”. Any thoughts?
You do not have to click “Run” there … just use this function in spreadsheet, like any other formula: =someMethod(“A1:A10”) etc… + pls check my updated post related to implementation, it could help you too 😉
What if i want to add a criteria?
Just extend IF condidion in script 😉