How do I count coloured cells in a Google Sheet?

NEEEEERRRRRDDD!

There, I’ve got it out of the way for both of us. Now we can carry on with this super handy tip for using Google Sheets!

Have you ever been sent a spreadsheet where someone’s colour-coded the cells? Rather than using a cell entry that would enable filtering, searching and every other usual function of a spreadsheet. Colour-coded. Not conditionally formatted colour-coding, so you could still filter and search. They’ve just changed the background colour of some cells.

I think you get my point. If you use colour coding in a spreadsheet, you can’t filter to show only the ‘green’ cells. If instead you write “Yes” in a cell, you could add conditional formatting which automagically then turns that cell green without you having to format each one individually. That would be a smarter solution.

Sometimes shit happens though. You end up with a large spreadsheet where the option of manually making this kind of correction isn’t really there.

Well now, thanks to the miracle of AI, here’s a little Google Apps Script to fix it. I’m sure there are other options, but I’d never searched before. This is extremely simple to use though, so I like it!

How to count coloured cells in a Google Sheets spreadsheet:

Step 1. In your Google Sheet, go to Extensions > Apps Script.

Step 2. Delete any code in the editor and paste in the following:

function countColoredCellsBySample(rangeA1, colorRefA1) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeA1);
  var colorRef = sheet.getRange(colorRefA1);
  var targetColor = colorRef.getBackground();
  
  var bgColors = range.getBackgrounds();
  var count = 0;
  
  for (var i = 0; i < bgColors.length; i++) {
    for (var j = 0; j < bgColors[i].length; j++) {
      if (bgColors[i][j] == targetColor) {
        count++;
      }
    }
  }
  return count;
}

Step 3. Click the disk icon at the top and give it a simple name.

Step 4. Close the script editor, and go back to your spreadsheet.

Step 5. In your spreadsheet use this custom formula to start your counting:

=COUNTCOLOREDCELLSBYSAMPLE("B1:Z10", "A1")

Note the range in the example above is B1 to Z10. But obviously you just need to change that to the size of the data you’re using. Top left cell ref to bottom right cell ref is the format. The second cell ref in speech marks is a cell that has the colour you want to add up. You need to colour the background of this ‘title cell’ the same as the coloured cells you want to count.

Simple! If you want a script that filters or manipulates coloured cells in another way, you’ll have to ask your own AI for that…