This is a Google Spreadsheets script that allows summing up cells depending on their foreground/background color.
The approach used in this script is slow, cumbersome and error prone. Please consider using other approaches if available. For example, you could label your data in additional columns/rows and use the built-in function SUMIF, to conditionally sum a range of columns.
If there is no alternative, or it's too hard to find one, go ahead and use this script as a last resort.
Installing the script will make the following functions available in the spreadsheet:
-
getBackgroundColor(<cell specification>, [<spreadsheet key>]) -
getForegroundColor(<cell specification>, [<spreadsheet key>]) -
sumWhereBackgroundColorIs(<color>, <range specification>, [<spreadsheet key>]) -
sumWhereForegroundColorIs(<color>, <range specification>, [<spreadsheet key>]) -
sumWhereBackgroundColorIsNot(<color>, <range specification>, [<spreadsheet key>]) -
sumWhereForegroundColorIsNot(<color>, <range specification>, [<spreadsheet key>])
The <spreadsheet key> parameter is optional in all cases. It should be provided in cases where the cells to be summed up are in a different spreadsheet. If it is not provided, the current spreadsheet will be used by default. Here's how to find the spreadsheet key: link
Please note that <range specification> and <cell specification> are expressed in A1 notation, and must be enclosed in quotes.
For example, to compute the sum of all the cells in the range B2:F13 that have the background color set to white, you should enter the following formula:
=sumWhereBackgroundColorIs("white", "B2:F13")
Some cells may not have the background set to a color such as 'white', 'gray', but a RGB color like #6fa8dc. You cannot guess what the color is, so if you want to find out the color for a cell (for example, B9), you should enter this formula in a cell:
=getBackgroundColor("B9")
and afterwards use this value as a parameter to the two functions above.
Information on how to install the script directly from the gallery can be found here.
Google Spreadsheet will cache the values computed with the functions described above. Therefore, if values change, the sum will not be updated.
This is because <range specification>s are passed as strings, and there is no way for the spreadsheet to "know" that the formula depends on that particular range of cells.
The solution is to add an additional parameter to the function, representing the "real" range of cells (not as a string).
For example, instead of:
=sumWhereBackgroundColorIs("white", "B2:F13")
you should write:
=sumWhereBackgroundColorIs("white", "B2:F13", "", B2:F13)
and now the sum should update whenever one of the "watched" values change. Note that in the above formula the third paremeter is a "", because it expects a spreadsheet key there.