Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 9 down vote accepted

Please see if my answer to a similar question helps.


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
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
Is there a way to make it update values automatically? – domen Nov 30 '13 at 13:00
@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
@w0lf The new Google spreadsheets seem to no longer support the script gallery. It redirects with a message to the new add-ons. The script editor is still available and I used your code there. I made a fork and added support for skipping empty (non-numeric) cells in the sum (otherwise an error occurs if empty cells exist in the range): gist.github.com/jeffdgr8/1553faa6360ad04e9c17 – Jeff Lockhart Jun 30 '14 at 20:45

protected by Community Mar 6 '13 at 15:52

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

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.