Home > Back-end >  Google sheets Count cells by color if value in cell is equal to 1 or more
Google sheets Count cells by color if value in cell is equal to 1 or more

Time:08-15

I'm working in Google sheets. I'm not a developer but I got this function from a YouTube video. In my Google sheet I have a list of task in column A and these tasks award 0-5 points when complete. The function I have looks at the range specified counts how many of them are green. works fine but I only interested in counting how many tasks are complete that award points of 1 or more. Here is the function:

function countColoredCells(countRange,colorRef,rangeSum) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var range = activeSheet.getRange(countRange);
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();
  
  var bg = range.getBackgrounds();  
  var colorCell = activeSheet.getRange(colorRef);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i = 0; i < rangeHeight; i  )
    for(var j = 0; j < rangeWidth; j  )
      if( bg[i][j] == color )
        count = count 1;
  
  return count;
};

To explain this code a bit more it takes 3 augments. The First points to a that has a range in it. In my sheet in cell C174 I have written there C2:C173. So I give the function C174 for the first augment and it looks there for the range. The second augment takes a cell with the color you want to be counted. So I have the G5 cell Colored Green so I give the functions second augment G5 because I want it to count the green cells. The third augment is a little tricky to explain but you give the function a cell that as a Boolean check box thing and when you change it's value it refreshes the function to update it's count. Here is my google sheets enter image description here

  • Related