Counting non empty cells with matching font color in Google Sheets

user_19340586 0
Viewed 31

I'm using a custom function to count the number of cells with a matching font color in a range.

function countIfFont(countRange,fontRef,rangeSum) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var range = activeSheet.getRange(countRange);
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();
  var fc = range.getFontColors(); 
  var fontCell = activeSheet.getRange(fontRef);
  var font = fontCell.getFontColor();
  var count = 0;
  
  for(var i = 0; i < rangeHeight; i++)
    for(var j = 0; j < rangeWidth; j++)
      if(fc[i][j] == font)
        count = count+1;
  
  return count;
};

It works, but it is counting blank cells and I want it to exclude blank cells. How do I check if the cell is blank?

google-apps-script google-sheets
1 Answers

In your script, how about the following modification?

From:

for(var i = 0; i < rangeHeight; i++)
  for(var j = 0; j < rangeWidth; j++)
    if(fc[i][j] == font)
      count = count+1;

To:

var values = range.getDisplayValues();
for (var i = 0; i < rangeHeight; i++)
  for (var j = 0; j < rangeWidth; j++)
    if (fc[i][j] == font && values[i][j])
      count = count + 1;
  • By this modification, when fc[i][j] == font and the cell is not empty, count = count + 1 is run.
user_71086530
Related