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?
Try (B1 contains a checkbox)
=sumColor(A:A,B1)
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;
};