Count coloured cells on Google Sheets with reference

In a spreadsheet if you need to count cells based on their background you can use this code in Google…

May 22, 2020

In a spreadsheet if you need to count cells based on their background you can use this code in Google Script editor.

The Google Script editor is in your Google Sheet, just open the Tools menu and choose Script editor.

function countColoured(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  } 
  var c = 0;
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      c = c + ( range.getCell(i,j).getBackground() == "#ffffff" ? 0 : 1 ); 
    }
  }
  return c > 0 ? c : "" ;
}
example of usage of countColoured formula

This function is different from other functions found online because this one accepts references, and you don’t have to pass strings with ranges (such as “A1:A100“). In a cell of your sheet you can use =countColoured(A1:A100)

As you can see the function parses the formula in the cell to extract the range of cells to be analyzed. Then it cycles on those cells and count look the background, when different from white (#ffffff) it adds 1.

Finally the total count is returned as the result.

The cells with this formula are automatically refreshed (updated) when values changes, but not when background color changes. So to update the count you have to trigger it manually by changing a cell in the spreadsheet.

Author

I'm a software engineer, an everyday web developer and a maker. I usually build sites with PHP, within or without WordPress. I build Internet of Things with Arduino and ESP8266. I'm the founder of Rockit.it and Dailybest.it and I'm actually the Chief Technical Officer of Better Days web agency.

Comments on “Count coloured cells on Google Sheets with reference”

Leave a Reply

Your email address will not be published. Required fields are marked with an *

Recommended

Social buttons: the fastest way for WordPress, without plugins

NOTE: the code in this post is written for WordPress but you can easily translate it in any language. You’re here…

September 15, 2015

Get Google Plus Follower count from PHP in WordPress

I’ve found a similar function to retrieve the number of followers of a Google Plus page, inside WordPress, but it…

May 7, 2014

Refresh a Google Adsense banner with Javascript

Often bloggers and site owners use galleries to have a greater number of pages, adding pages means adding clicks. Each…

November 9, 2013

Top Social Stories plugin and widget for WordPress

Just released a plugin for WordPress that can be used to track your posts on Facebook, Twitter and Google+. When…

November 3, 2013

PHP google images mini bot

UPDATE 2013/12/12: Now, in the  Mini Bot Class there is a version of this function that is still working. You…

March 30, 2010

Decimal Degrees conversion and distance of two points on google map

Those two functions are usefull when you’re making Google Maps applications: When you show the coordinates of a point, it’s…

December 26, 2009