Looking for a formula to give the sum of all cells with the same color text

Question

I have a sheet with entries in 4 different colors. I would like a Sum Formula for the total value of all entries of each color. The sums will be displayed in separate cells.

First row is locked with Titles. First column contains dates. Currently there's columns B - H containing entries and 215 rows but I would like to be able to expand this and have the values captured in the total without changing the formula if possible.

Color Codes: Green #34a853 Orange #ff6d01 Blue #4285f4 Red #ea4335

I would like the result to be in cells J3-J6 after the corresponding color. TIA!

I've tried using the filter function to filer the colors but it's not what I'm looking for. I've tried a few scripts but can't seem to get them dialed in. I'm new to this and all were adopted from other posts online. Sample

Answer 1

Method 1: running the script to automatically place the SUM where you've mentioned:

function calculateColorSums() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getRange("B2:H" + sheet.getLastRow()); // Adjust the range as needed
  var data = dataRange.getValues(); // Fetch data
  var textColors = dataRange.getFontColors(); // Fetch text colors

  // Define colors and initialize sums
  var colors = {
    "Green": "#34a853",
    "Orange": "#ff6d01",
    "Blue": "#4285f4",
    "Red": "#ea4335"
  };

  var sums = {
    "Green": 0,
    "Orange": 0,
    "Blue": 0,
    "Red": 0
  };

  // Iterate through the data and calculate sums based on text colors
  for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
      var cellValue = data[row][col];
      var cellColor = textColors[row][col];

      // Check if the text color matches a predefined color
      for (var color in colors) {
        if (cellColor === colors[color]) {
          sums[color] += Number(cellValue); // Convert the cell value to a number and add to the sum
        }
      }
    }
  }

  // Write the sums to cells J3-J6
  var results = [["Green", sums["Green"]], ["Orange", sums["Orange"]], ["Blue", sums["Blue"]], ["Red", sums["Red"]]];
  sheet.getRange("J3:K6").setValues(results);
}

    
    // Log a success message
    Logger.log("Color sums calculated and updated successfully.");
  } catch (e) {
    // Log any errors that occur
    Logger.log("Error: " + e.toString());
  }
}

Method 2: using a custom formula (since you've mentioned it):

function COLORSUM(color) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getRange("B2:H"); // Update with your desired range
  var values = dataRange.getValues();
  var textColors = dataRange.getFontColors();

  var sum = 0;

  for (var row = 0; row < values.length; row++) {
    for (var col = 0; col < values[row].length; col++) {
      if (textColors[row][col] === color) {
        var cellValue = values[row][col];
        if (!isNaN(cellValue) && cellValue !== "") {
          sum += parseFloat(cellValue);
        }
      }
    }
  }

  return sum;
}

Then use this custom formula wherever you want in the spreadsheet:

For "Green": "#34a853" it will be: =COLORSUM("#34a853")

Answer 2

function sumColors() {
  const excl = ["#ffffff"];//excluded colors
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const sr = 3;//data start row
  const cr = 1;//result row
  const rg = sh.getRange(2,1,sh.getLastRow() - sr + 1,sh.getLastColumn());
  const vs = rg.getValues();
  const bs = rg.getBackgrounds();
  let obj = {pA:[]};
  bs.forEach((r,i) => {
    r.forEach((c,j)=> {
      if(!~excl.indexOf(c) && !isNaN(vs[i][j])) {
        if(!obj.hasOwnProperty(c)) {
          obj[c] = vs[i][j];
          obj.pA.push(c);
        } else {
          obj[c] += vs[i][j];
        }
      }
    })
  })
  obj.pA.forEach((p,i) => {
    sh.getRange(cr,i+1).setValue(obj[p]);
    sh.getRange(cr,i+1).setBackground(p);
  })
}

It only sums cells that are numbers