How to Count Colored Cells in Google Sheets [3 Methods]

How to Count Colored Cells in Google Sheets 29
Key Takeaways: How to Count Colored Cells in Google Sheets

To Count Colored Cells in Google Sheets

Using App Script

  1. Copy the provided App script code.
  2. Open your Google Sheets file.
  3. Go to “Extensions” > “Apps Script“.
  4. Paste the script > Save it.
  5. In your sheet, enter the function with the range and color reference.
  6. The script counts colored cells and displays the result.

Using Filters

  1. Color your data.
  2. Create a filter for your data.
  3. Use “Filter by Color” to show cells of a specific color.
  4. Add a Subtotal function to count the filtered cells.

Using Add-on

  1. Install the “Power Tools” add-on.
  2. Open your Google Sheets file.
  3. Launch the Power Tools add-on.
  4. Select “Calculate cells based on their colors“.
  5. Choose the range and color to count.
  6. Specify the function (e.g., COUNTA).
  7. 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

How to Count Colored Cells in Google Sheets 1

Step 2

Copy the below code

</pre>
function countColoredCells(countRange,colorRef) {

var activeRange = SpreadsheetApp.getActiveRange();

var activeSheet = activeRange.getSheet();

var formula = activeRange.getFormula();

&nbsp;

var rangeA1Notation = formula.match(/\((.*)\,/).pop();

var range = activeSheet.getRange(rangeA1Notation);

var bg = range.getBackgrounds();

var values = range.getValues();

&nbsp;

var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();

var colorCell = activeSheet.getRange(colorCellA1Notation);

var color = colorCell.getBackground();

&nbsp;

var count = 0;

&nbsp;

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;

};

How to Count Colored Cells in Google Sheets 2

Step 3

Go to your Sheets file, and in the Extension, menu click on the Apps Script

How to Count Colored Cells in Google Sheets 3

Step 4

A new browser tab will open, and you will your code editor there.

How to Count Colored Cells in Google Sheets 4

Step 5

Clear the code if something is already written in the code editor, and paste the copied code here.

How to Count Colored Cells in Google Sheets 5

Step 6

Now click on the Save button above

How to Count Colored Cells in Google Sheets 6

Step 7

From the first line of the code, note or copy the function name (it will be used when applying the function)

How to Count Colored Cells in Google Sheets 7

Step 8

Now, write the function name as a normal function starting with an equal sign, and open brackets

How to Count Colored Cells in Google Sheets 8

Step 9

For the first argument, pass the entire range in which you want to count colored cells

How to Count Colored Cells in Google Sheets 9

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.

How to Count Colored Cells in Google Sheets 10

Step 11

Press Enter, and you can see that you got the count of the color you passed.

How to Count Colored Cells in Google Sheets 11

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

How to Count Colored Cells in Google Sheets 12

Step 2

Now color your data as you want

How to Count Colored Cells in Google Sheets 13

Step 3

Now create a filter on your data.

Select the data, and then go to Data > Create a filter

How to Count Colored Cells in Google Sheets 14

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

How to Count Colored Cells in Google Sheets 15

Step 5

Now you need a Subtotal function, add a Subtotal function below the data

How to Count Colored Cells in Google Sheets 16

Step 6

Pass the first argument default as 103, and the second argument as the column that needs to be counted.

How to Count Colored Cells in Google Sheets 17

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.

How to Count Colored Cells in Google Sheets 18

How to Count Colored Cells in Google Sheets 19

How to Count Colored Cells in Google Sheets 20

How to Count Colored Cells in Google Sheets 21

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

How to Count Colored Cells in Google Sheets 22

Step 2

Search for “Power tools

How to Count Colored Cells in Google Sheets 23

Step 3

Click on the power tools in the results and again click on the Install button

How to Count Colored Cells in Google Sheets 24

How to Count Colored Cells in Google Sheets 25

Step 4

Read the message before allowing access to the application

How to Count Colored Cells in Google Sheets 36

Step 5

Connect your Google account to continue to Install

How to Count Colored Cells in Google Sheets 27

Step 6

Once you’re done with the installation setup, now back to the Sheets and open Power tools

How to Count Colored Cells in Google Sheets 28

Step 7

Click on the button “Calculate cells based on their colors

How to Count Colored Cells in Google Sheets 29

Step 8

Click on Function by color

How to Count Colored Cells in Google Sheets 30

Step 9

Select the range in which you want to count the cell colors

How to Count Colored Cells in Google Sheets 31

Step 10

Select a function to perform on the cells having the defined colors

Select COUNTA from the list

How to Count Colored Cells in Google Sheets 32

Step 11

Select the cell in which you want to get the result

How to Count Colored Cells in Google Sheets 33

Step 12

Click on the Insert function button to get the result

How to Count Colored Cells in Google Sheets 34

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.

How to Count Colored Cells in Google Sheets 35

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general