Is there a formula to calculate the number of cells in a column that are shaded (or not shaded) a certain color?

For example: 20 of the cells are gray and 31 are not gray (shaded any other color) and instead of manually counting them, it calculates this at the bottom of reach column.

• The problem with the @w0lf's solution comes from the cache. The function is not ready to get live updates. I tried to gain that ability by adding "now()" as an extra parameter but it didn't work. Oct 30 '13 at 19:36
• I can't answer so I'll just leave a comment. If the cells dont have any text, it is easier to add any text inside with the same color as the background (so it is hidden) and then you just do a simple =counta() to count non empty cells. If course this is very specific depending on the case. Feb 20 '20 at 15:37

I have found a way to do this, using the scripting functionality that Google Spreadsheet provides.

Here's how to do it:

• 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 cell = sheet.getRange(rangeSpecification);
return cell.getBackground();
}

function sumWhereBackgroundColorIs(color, rangeSpecification) {
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 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.

• I have been trying to follow your hints, but in the field I use the formula I receive #NUM!, do you know why is that? I tried to do some research, but I couldn't find exact answer for that. Thanks a million, dami
– user42258
Jun 18 '13 at 14:02
• @Dami can you post your formula here? Also, check the spelling - I think the formula names are case sensitive Jun 18 '13 at 15:28
• Getting just a zero here, despite following the instructions...
– user43718
Jul 15 '13 at 4:17
• The `getBackgroundColor()` is deprecated. Perhaps you can update the code? Sep 27 '13 at 13:36
• When I change the background color to another value, the sum is not updated. Is there a way to automatically update the sum when I do some changes? Nov 12 '13 at 9:29

I have not found a way to directly count the cells with a specific color in Google Docs.

What makes them shaded. Is it automatic? If they have a value they are automatically shaded? If that is the case you can use the counta() function to count any cell with a value. If there is a more complex set of rules involved you may need to use the countif() function.

Note:you may have to take into account any extra rows at the top or the bottom of the column