Tag: spreadsheet tricks

🎨 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;
}

3. Click the 💾 Save icon and give your project a name.
4. 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!

---