How to Count or Sum Cells by Color in Google Sheets (with Script)

🎨 How to Count or Sum Cells Based on Cell Color in Google Sheets

Google Sheets doesn’t have built-in functions like COUNTBYCOLOR or SUMBYCOLOR, but you can achieve this using Google Apps Script or manual helper columns if using conditional formatting.


✅ Method 1: Using Google Apps Script (Best for Manual Cell Colors)

You can create custom functions to count or sum cells by fill color.

🔧 Step-by-Step:

  1. Click Extensions > Apps Script
  2. Delete any existing code and paste the following:
javascriptCopyEdit// Function to count cells with a specific background color
function countColoredCells(range, color) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(range);
  var bgColors = range.getBackgrounds();
  var count = 0;
  
  for (var i = 0; i < bgColors.length; i++) {
    for (var j = 0; j < bgColors[i].length; j++) {
      if (bgColors[i][j] == color) {
        count++;
      }
    }
  }
  return count;
}

// Function to sum cells with a specific background color
function sumColoredCells(range, color) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(range);
  var bgColors = range.getBackgrounds();
  var values = range.getValues();
  var sum = 0;
  
  for (var i = 0; i < bgColors.length; i++) {
    for (var j = 0; j < bgColors[i].length; j++) {
      if (bgColors[i][j] == color) {
        sum += parseFloat(values[i][j]) || 0;
      }
    }
  }
  return sum;
}
  1. Click the 💾 Save icon and give your project a name.
  2. Back in your sheet, use:
excelCopyEdit=countColoredCells("A1:A10", "#ffff00")
=sumColoredCells("A1:A10", "#ffff00")

Replace "A1:A10" with your range and "#ffff00" with the actual color hex code (like Yellow).

📝 You can find the hex code by:

  • Selecting a colored cell
  • Right-click → View more cell actions > Get cell color hex code

⚠️ Notes:

  • This script works only for manually colored cells.
  • It does not detect conditional formatting colors.

✅ Bonus: If Using Conditional Formatting

If color is based on a condition (e.g., values > 100), don’t rely on the color — instead use the same logic:

excelCopyEdit=COUNTIF(A1:A10, ">100")
=SUMIF(A1:A10, ">100")

Always use the logic that drives the color rather than the color itself.


🚀 Want to Learn All These Pro Techniques?

Everything from custom formulas, conditional formatting, scripts, and data tools is covered in my premium Google Sheets course!

🔗 📘 Enroll Now – Unlock the Power of Google Sheets

💥 Limited-Time Offer: ₹1,299 → ₹449 Only!

🎯 Course Features:

  • 29 detailed video lessons
  • 3h 46m of content from beginner to advanced
  • Covers formulas, charts, pivot tables, automation, Apps Script, and more
  • Ideal for students, professionals, and business users

💡 Take control of your spreadsheets and become a data ninja with this course!