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 below
- 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
Code:
function getBackgroundColor(rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSheet();
var cell = sheet.getRange(rangeSpecification);
return cell.getBackground();
}
function sumWhereBackgroundColorIs(color, 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.getBackground() == color)
x += parseFloat(cell.getValue());
}
}
return x;
}
function countCellsWithBackgroundColor(color, 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.getBackground() == color)
x++;
}
}
return x;
}
After following the above steps, you have three more functions available in the spreadsheet:
countCellsWithBackgroundColor(<color>, <range specification>)
sumWhereBackgroundColorIs(<color>, <range specification>)
getBackgroundColor(<cell 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:
=countCellsWithBackgroundColor("white", "B2:F13")
and to compute the sum of the same cells, use the 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.
It appears that if a cell's background color is a "theme color," getBackground()
incorrectly always returns #ffffff
. I haven't found a way around this other than to use a color from the standard set.
Again, note the quotes in all above examples; without them, the formulas would not work.