Ever found yourself in a situation where you needed to count cells based on their color in Google Sheets? While Google Sheets doesn't have a built-in COUNTIF function for colored cells, there are workarounds to achieve this task. Let's explore how you can count colored cells in Google Sheets.

Before we delve into the methods, it's essential to understand that Google Sheets uses the COLOR command to set cell color. So, we'll first set the cell color in the functions we use to count them. Now, let's get started.

Using COUNTIF Function with ISBLANK and NOT Functions
The COUNTIF function in Google Sheets is highly versatile and can be used to count based on conditions. By combining it with the ISBLANK and NOT functions, we can create a workaround to count colored cells. Here's how:

The syntax for this formula is: `=COUNTIF(NOT(ISBLANK(A2:A10)), "")`. This formula will count the non-blank cells in the range A2:A10. To count colored cells, we'll set these cells to a specific color using the COLOR function before applying this formula.
Setting Cell Color with COLOR Function

To set cell color, use the COLOR function followed by the range of cells you want to color. For example, if you want to set cells A2 to A10 to red, you would use: `=ARRAYFORMULA(IF((A2:A10 > "0")), COLOR(255, 0, 0)))`. This will set all non-zero values in the range to red.
To set cells to a specific color based on a condition, you can combine the COLOR function with an IF or IFERROR function. For example, to set positive numbers to green and negative numbers to red, you would use: `=ARRAYFORMULA(IF(A2:A10 > 0, COLOR(0, 255, 0), COLOR(255, 0, 0))))`.
Counting Colored Cells

After setting the cell color, you can now use the COUNTIF function with ISBLANK and NOT to count these colored cells. For instance, to count the red cells we set in the previous example, you would use the formula: `=COUNTIF(NOT(ISBLANK(A2:A10)), "")`.
To count cells with specific colors, you'll need to adjust the RGB values in the COLOR function accordingly. For example, to count cells with a light blue color (RGB: 135, 206, 235), you would use: `=COUNTIF(NOT(ISBLANK(A2:A10)), COLOR(135, 206, 235))`.
Using Google Apps Script

Another method to count colored cells involves using Google Apps Script. This method might be more efficient if you're working with a large number of cells. Let's look at an example:
The following script can be used as a custom function in Google Sheets. To use it, create a new Script project, paste the function below, and save it with a function name (e.g., 'COUNT_COLORED_CELLS'). Then, you can use this function in your sheet like this: `=COUNT_COLORED_CELLS(A2:A10, "red")`.




















Creating the Custom Function
The script uses the Range.getBackgrounds() method to get the color of each cell in the specified range. It then checks if this color matches the color you specified (in this case, red). If it does, it adds the cell to a counter. Here's the script:
`function COUNT_COLORED_CELLS(range, color) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getActiveSheet(); const array = range.getValues(); let count = 0; const rgb = color.toLowerCase().split(''); if (rgb.length === 3 && !isNaN(rgb[0]) && !isNaN(rgb[1]) && !isNaN(rgb[2])) { for (let i = 0; i < array.length; i++) { const background = sheet.getRange(i + 1, range.getColumn()).getBackground(); if (background === color || background.toLowerCase() === 'rgb(' + rgb.join(',') + ')') { count++; } } } return count; }`
The script checks if the specified color is a valid RGB value. If it is, it compares each cell's color in the range to the specified color. If there's a match, it increments the counter. This function can count cells with specific colors based on their RGB values.
Counting colored cells in Google Sheets can be a useful task, especially when you need to analyze data based on its visual representation. Although Google Sheets doesn't support direct counting of colored cells, the methods described above provide effective workarounds. Now that you know how to count colored cells, you can use this knowledge to streamline your data analysis and bring efficiency to your workflow.