I am using Google spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. 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. –  warmth Oct 30 '13 at 19:36

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 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>)
• 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.

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.

-
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 –  w0lf 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? –  Jacob Jan Tuinstra Sep 27 '13 at 13:36
@JacobJanTuinstra I have updated the code, but it takes a while for Google to review my submission. In the meantime, you can use this code –  w0lf Sep 27 '13 at 14:34
show 1 more comment

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

-