To Count Colored Cells in Google Sheets
Using App Script
- Copy the provided App script code.
- Open your Google Sheets file.
- Go to “Extensions” > “Apps Script“.
- Paste the script > Save it.
- In your sheet, enter the function with the range and color reference.
- The script counts colored cells and displays the result.
Using Filters
- Color your data.
- Create a filter for your data.
- Use “Filter by Color” to show cells of a specific color.
- Add a Subtotal function to count the filtered cells.
Using Add-on
- Install the “Power Tools” add-on.
- Open your Google Sheets file.
- Launch the Power Tools add-on.
- Select “Calculate cells based on their colors“.
- Choose the range and color to count.
- Specify the function (e.g., COUNTA).
- Click the “Insert function” for the count result.
Hello everyone and welcome to another tutorial of our Google Sheets Series. Today, we will learn how to count colored cells in Google Sheets. We use so many colors in our Google Sheets file for changing the visibility of different sections or rows of our data, the primary reason is to make the data look more beautiful and clearer. Firstly, there is no direct logic in Google Sheets to count colored cells but we have some workarounds to count colored cells in Google Sheets.
Today I will teach you three easy methods to count colored cells in Google Sheets. These colored cells can be directly colored, or even if they are colored by conditional formatting, the methods work identically in both conditions. So, let’s get started.
Use case of Counting Colored Cells in Google Sheets
Colors are one of the major attributes of our documents. Sometimes, colors are used only for aspects, and sometimes, they are playing an important role in the clarity of the data. Most commonly, people use colors to segmentize the data, such as the December data being colored with blue, October data colored with yellow, and so on. OR, people also use colors for coloring successful or profit data with green, and failure or lost data with red, so colors are very helpful in many ways. When using similar colors, the data is visualized very accurately. A reader does not have to read the entire data if the colors are used accurately as per the data type.
Colors can fill color, text color, or highlight color, today we will learn about fill colors. They are also called cell colors; they are most commonly used to make data more precise and clearer. Fill color can be added directly from the toolbar or can also be added by conditional formatting.
When we keep our data color-wise, then the need of counting those colored values becomes very significant. There is no direct method or function, or formula to count colored cells in Google Sheets, but we use some workarounds to do that and get accurate results just like the normal COUNT function in Google Sheets. So, these are some reasons you need to learn how to count colored cells in Google Sheets.
How to Count Colored Cells in Google Sheets?
Here we will learn all the methods in detail. We have three methods, and we will go through each very in-depth and see the step-by-step procedures and requirements to perform those methods practically on some sample data. The first method is the App script method, we have an already written code, and we can use it for free to count colored cells in Google Sheets, then we have two more easy methods. So, let’s get started.
How to Count Colored Cells in Google Sheets – Using App Script
The app script is the most powerful thing inside Google Sheets. Here, we can write customized programs to do whatever we want, the code should be programmatically valid, and almost anything can be done using an app script code. So, we have a code to count colored cells, you just need to copy it from below and paste it to your sheet to start working.
Step 1
Open a Google Sheets file, or launch a new Sheet with a blank template
Step 2
Copy the below code
</pre> function countColoredCells(countRange,colorRef) { var activeRange = SpreadsheetApp.getActiveRange(); var activeSheet = activeRange.getSheet(); var formula = activeRange.getFormula(); var rangeA1Notation = formula.match(/\((.*)\,/).pop(); var range = activeSheet.getRange(rangeA1Notation); var bg = range.getBackgrounds(); var values = range.getValues(); var colorCellA1Notation = formula.match(/\,(.*)\)/).pop(); var colorCell = activeSheet.getRange(colorCellA1Notation); var color = colorCell.getBackground(); var count = 0; for(var i=0;i<bg.length;i++) for(var j=0;j<bg[0].length;j++) if( bg[i][j] == color ) count=count+1; return count; };
Step 3
Go to your Sheets file, and in the Extension, menu click on the Apps Script
Step 4
A new browser tab will open, and you will your code editor there.
Step 5
Clear the code if something is already written in the code editor, and paste the copied code here.
Step 6
Now click on the Save button above
Step 7
From the first line of the code, note or copy the function name (it will be used when applying the function)
Step 8
Now, write the function name as a normal function starting with an equal sign, and open brackets
Step 9
For the first argument, pass the entire range in which you want to count colored cells
Step 10
As the second argument, pass the cell having the color you want to count for example B3 has a green color, so pass the reference of the cell B3.
Step 11
Press Enter, and you can see that you got the count of the color you passed.
This is how to count colored cells in Google Sheets very easily and very quickly using the app script code.
How to Count Colored Cells in Google Sheets – Filter and Subtotal
In this section, we will learn how to count colored cells in Google Sheets using Filters and a subtotal function. This is also a good workaround to use to count colored cells. Let’s see in detail how this process works, and do we use the combination of internal filters and the Subtotal function to do that?
Step 1
Sample data
Step 2
Now color your data as you want
Step 3
Now create a filter on your data.
Select the data, and then go to Data > Create a filter
Step 4
A filter will be created for your data, now you can click on the filter, go to Filter by Color and then select a color and you will have only those colored rows visible
Step 5
Now you need a Subtotal function, add a Subtotal function below the data
Step 6
Pass the first argument default as 103, and the second argument as the column that needs to be counted.
Step 7
Now, all you need to do is apply filter by color for one color, and below the subtotal will give you the count of that color, and then similarly filter for the second color.
So, this is how using the Sheets filter and Subtotal function you can make a scenario to count colored cells in Google Sheets.
How to Count Colored Cells in Google Sheets – Using Power Tool Add-on
In this section, we will learn how to count colored cells in Google Sheets using an add-on. This add-on is called “Power tools” and is very commonly used for counting colored cells and some other functions as well. So, let’s get the add-on first from the Google Workspace marketplace and see how to use it to count colored cells in Google Sheets.
Step 1
Go to Extensions > Add-ons > get add-ons
Step 2
Search for “Power tools”
Step 3
Click on the power tools in the results and again click on the Install button
Step 4
Read the message before allowing access to the application
Step 5
Connect your Google account to continue to Install
Step 6
Once you’re done with the installation setup, now back to the Sheets and open Power tools
Step 7
Click on the button “Calculate cells based on their colors”
Step 8
Click on Function by color
Step 9
Select the range in which you want to count the cell colors
Step 10
Select a function to perform on the cells having the defined colors
Select COUNTA from the list
Step 11
Select the cell in which you want to get the result
Step 12
Click on the Insert function button to get the result
Step 13
Here is the result, you can see the count of red in red color, and if you do the same with other colors you will see the result with those colors for your easiness and clarity.
So, this is how you can Power tools Google Sheets extension to count colored cells in Google Sheets.
I hope you find all these methods helpful.
Frequently Asked Questions
How to count colored cells in Google Sheets?
There are three good ways to count colored cells in Google Sheets. The first method is using an App script and getting the count of colored cells in Sheets. The second method is a little extensive where we first need to create a filter on the overall data, and then we use the filter by color to only show single-colored data, and when the filter is active, we have a function called subtotal that counts, and total the number of rows showing and give us a number which is the count of the colored cells. And, lastly, we saw another quick method that used a Google Sheets add-on called Power tools. Power tools have so many features, they are free and provided by Ablebits. We installed this add-on and we perform some steps to count colored cells in Google Sheets. This add-on also gives you the power to count rows only and columns only. What we did we count all the same-colored cells in a data set including rows and columns.
How to count colored cells that are colored using conditional formatting?
There is no difference in counting colored cells that are colored normally from the fill color tool, or by conditional formatting rules. We can use all three methods in both situations.
How to count update change if the color is changed after I have applied a Script in my Sheets file?
Now, Google Sheets has updated this feature of automatic updating on any change, so when you change color or it’s changed by the conditional formatting rules, there is nothing to do to update the data. On any change, the data will update automatically.
Can I Use Conditional Formatting to Count Colored Cells in Google Sheets?
Yes, you can use conditional formatting to count colored cells in Google Sheets. By applying conditional formatting rules using the Custom Formula option, you can easily count cells based on their color. For instance, to count cells with changing text color with formatting, use a formula like =countif(A1:A10, ‘changing text color with formatting’).
Conclusion
So that’s from how to count colored cells in Google Sheets. I tried to cover all possible methods to count colored cells in Google Sheets. I hope you find the above article useful and that it helped you count the colored cells in Google Sheets. I will see you soon with another helpful guide till then take care. Have a nice day, and keep learning with Office Demy.