Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Count or Sum Cells based on Cell Color in Google Sheet?
In this article, we'll look at a useful tool that lets you make calculations depending on the colour of spreadsheet cells. Utilising this feature will allow you to swiftly analyse and draw conclusions from data that has been visually distinguished by colour coding. Google Sheets offers a variety of built-in functions and custom scripts to complete these tasks, whether you want to count the number of cells that are a given colour or calculate the total of values connected to a particular colour. In order to accommodate a range of user preferences and ability levels, this lesson will cover a number of techniques for counting or adding cells based on their colour.
We'll presume you have a fundamental understanding of Google Sheets and are familiar with its user interface throughout this course. To make sure you can easily follow along, we will offer step-by-step directions and screenshots. So let's get started and discover the potential of Google Sheets' cell color-based computations!
Count or Sum Cells Based on Cell Color
Here we will first add a script to the sheet and then use a formula to complete the task. So let us see a simple process to know how you can count or sum cells based on cell colour in a Google Sheet.
Step 1
Consider a Google Sheet where you have different cell colours.
First, click on Extensions and select App Scripts.
Extension > App Scripts.
Step 2
Then replace the existing code with the below code and save the code.
Code
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
Step 3
Then click on an empty cell and enter the formula as =countcoloredcells(A2:C7,E2) and click enter to get the first value.
Empty Cell > Formula > Enter.
Then repeat this step for all the colours.
Note
If you want to calculate the sum, use the below code and use the formula =sumcoloredcells(A2:C7,E2).
Code
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can count or sum cells based on cell colour in a Google Sheet to highlight a particular set of data.
6K+ Views
