Please see if my answer to a similar question helps.
Update:
My submission for the Google Docs Script gallery was accepted and you can install it to get the functionality you wish.
The steps:
- Open your spreadsheet
- In the menu go to Tools -> Script gallery...
- Search for Sum by color
- Click Install
- Click the Authorize button if you trust the script and then Close
- go back to the spreadsheet
Now you have three additional functions you can use in your spreadsheet formula:
sumWhereBackgroundColorIs(color, rangeSpecification)
sumWhereBackgroundColorIsNot(color, rangeSpecification)
getBackgroundColor(rangeSpecification)
Please from that the rangeSpecification
parameters require quotes ("
) around them.
For example:
=sumWhereBackgroundColorIs("white", "A1:C4")
sums the values of all cells in the A1:C4 range that have a white background.
Please note that if you don't know the color of a certain cell you can use the provided getBackgroundColor
function to find out what the color is. This function is necessary, because some colors are expressed as RGB codes (for example, #00ff00
instead of green
).