I have a table with cells that contain numbers styled with fonts of different colors. I would like to sum all cell values of a certain color and output that sum into a separate cell. How can I do that?

CodePudding user response：

Try (B1 contains a checkbox)

=sumColor(A:A,B1)

• put the formula in a range with the same color as the data to be added
• add a check box to allow you updating the value when colors will be changed
• the formula can be located in another sheet

with this custom function

function sumColor(range) {
  var r = SpreadsheetApp.getActiveRange();
  var color = r.getFontColors();
  var total = 0;
  var addresses = r.getFormula().match(/(?<=\().*(?=\))/g)[0].split(/[;|,]/)
  for (var i = 0; i < addresses.length - 1 ; i  ) {
    try {
      var sh = SpreadsheetApp.getSheetByName(addresses[i].split('!')[0].replace("'", ""));
      var address = addresses[i].split('!')[1].trim();
    }
    catch (e) {
      var sh = SpreadsheetApp.getActiveSheet();
      var address = addresses[i].trim()
    }
    var colors = sh.getRange(address).getFontColors();
    var values = sh.getRange(address).getValues();
    for (var j = 0; j < colors.length; j  )
      for (var k = 0; k < colors[i].length; k  )
        if (colors[j][k] == color)
          if ((typeof values[j][k]) == 'number') 
            total  = values[j][k];
  }
  return total;
};