How to count cells with specific color in Google Sheets?

Other Questions Google Sheets
1

Hey everyone! I’m working on a Google Sheets project and could use some help. I’ve got a bunch of dates in my spreadsheet, and I’ve used conditional formatting to color them either red or green. Now I’m trying to figure out how to count how many green cells are in a certain range.

Here’s what I’m hoping to do:

  1. Pick a specific range of cells
  2. Count how many of those cells are green
  3. Put that number in another cell

I’ve got three different date ranges I need to do this for. Is there a formula or script that can handle this? I’m not great with coding, so any tips would be super helpful! Thanks in advance for your help!

2

hey there! i’ve got a trick for this. try using the COUNTIF function with a custom formula. something like =COUNTIF(A1:C10, “=” & CHAR(10006)). replace A1:C10 with ur range and CHAR(10006) with the unicode for ur green checkmark. might need to tweak it, but should get u started!

3

You can count cells with a specific color by creating a custom function in the Google Sheets Script Editor. First, open your sheet, go to Tools > Script Editor, and paste the following code:

function COUNTCOLOR(range, color) {
return range.getValues().flat().filter(function(cell, i) {
return range.getBackgrounds().flat()[i] == color;
}).length;
}

Save the script. Then, in your spreadsheet, use the formula:

=COUNTCOLOR(A1:C10, “#00ff00”)

Replace A1:C10 with your desired range and “#00ff00” with the appropriate hex code for your green color. This method can be applied to different date ranges by adjusting the cell range in the formula.

4

Hey Zack, I’ve been in a similar situation before. Here’s what worked for me:

I used the QUERY function combined with ARRAYFORMULA. It’s a bit of a workaround, but it gets the job done without needing any scripts or custom functions.

The formula looks something like this:

=ARRAYFORMULA(SUM(–($A$1:$C$10=$A$1:$C$10)))

Replace A1:C10 with your actual range. This formula basically compares each cell to itself. If the cell has a background color, it’ll return 1, otherwise 0. Then it sums up all those 1s.

It’s not perfect - it’ll count any colored cell, not just green. But if you’re only using green and the default white, it should work fine.

Just copy this formula for each of your three date ranges, adjusting the cell references accordingly. Hope this helps!