Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

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

share|improve this answer
    
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

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

share|improve this answer

protected by Community Oct 30 '13 at 21:19

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.