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