I have found a way to do this, using the scripting functionality that Google Spreadsheet provides.
Here's how to do it:
- open your spreadsheet
- in the menu, go to Tools -> Script Editor...; this will open a new window that allows you to enter code
- copy the code found at http://pastebin.com/4Yr095hV
- paste the code in the Script Editor window and press CTRL+S to save it
- close the Script editor windows and go back to the spreadsheet
After following the above steps, you have three more functions available in the spreadsheet:
countCellsWithBackgroundColor(<color>, <range specification>)
sumWhereBackgroundColorIs(<color>, <range specification>)
Please note that
<range specification> and
<cell specification> are expressed in A1 notation, and must be enclosed in quotes.
For example, to get the count of all the cells in the range B2:F13 that have the background color set to white, you should enter the following formula:
and to compute the sum of the same cells, use the formula:
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:
and afterwards use this value as a parameter to the two functions above.
Again, note the quotes in all above examples; without them, the formulas would not work.
Note: I just submitted this code to the Script Gallery to make it a little easier to use, but it'll take a while for it to be available. When it is, I'll post an update here.