Google-sheets – Sum data based on cell color in Google Spreadsheets

google sheets

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?

Best Answer

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

Related Topic