Your cart is currently empty!
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:
- Click Extensions > Apps Script
- 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;
}
- Click the 💾 Save icon and give your project a name.
- 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!
Top rated products
-
MS Office Online Course: Basic to Advance Level
Original price was: ₹2,999.00.₹2,499.00Current price is: ₹2,499.00. -
The Psychology of Money: Timeless Lessons on Wealth, Greed, and Happiness
-
Mastering Excel Automation: Excel VBA Training Course for Proficiency and Efficiency
Original price was: ₹2,299.00.₹1,999.00Current price is: ₹1,999.00. -
Premium Gold Whey Protein
-
Lenovo SmartChoice Chromebook (82UY0014HA) – Compact & Affordable Everyday Laptop
-
The Purple Tree Star Curtain LED Lights for Diwali Decor
Original price was: ₹1,999.00.₹285.00Current price is: ₹285.00.