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 have seen this question and altough I face the same problem, the answer to that question is not helpful to my case.

I have large spreadsheets which are changed frequently. So, according to the solution accepted to that question, it is hard to duplicate all populated columns and it is cumbersome to edit one more cell everytime I wish to make a change on cells background colors.

Isn't there a way to sum data based on a condition regarding cell background color in Google Spreadsheets?

share|improve this question
add comment

1 Answer 1

Please see if my answer to a similar question helps.

Update:

My submission for the Google Docs Script gallery was accepted and you can install it to get the functionality you wish.

The steps:

  • Open your spreadsheet
  • In the menu go to Tools -> Script gallery...
  • Search for Sum by color
  • Click Install
  • Click the Authorize button if you trust the script and then Close
  • go back to the spreadsheet

Now you have three additional functions you can use in your spreadsheet formula:

  • sumWhereBackgroundColorIs(color, rangeSpecification)
  • sumWhereBackgroundColorIsNot(color, rangeSpecification)
  • getBackgroundColor(rangeSpecification)

Please from that the rangeSpecification parameters require quotes (") around them.

For example:

=sumWhereBackgroundColorIs("white", "A1:C4")

sums the values of all cells in the A1:C4 range that have a white background.

Please note that if you don't know the color of a certain cell you can use the provided getBackgroundColor function to find out what the color is. This function is necessary, because some colors are expressed as RGB codes (for example, #00ff00 instead of green).

share|improve this answer
1  
The getBackgroundColor() is deprecated. Perhaps you can update the code? –  Jacob Jan Tuinstra Sep 27 '13 at 13:36
1  
@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
1  
Is there a way to make it update values automatically? –  domen Nov 30 '13 at 13:00
1  
@domen yes, there is:github.com/clupascu/GoogleApps_SumByColor/blob/master/…. Not a very pretty solution, but it works. –  w0lf Nov 30 '13 at 20:25
    
Oh, now I feel silly, thanks! –  domen Dec 2 '13 at 9:34
show 5 more comments

protected by Community Mar 6 '13 at 15:52

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.