Count by Color in Google Sheets

 Hello everybody!

Are you familiar with the function COUNTIF in Google Sheets? You can count how many cells match a certain criteria from a range. But what if what you need is to count the color of the cells? That is not currently possible.

I came across a situation in which I had applied individual conditional formatting to 25 different columns. The words didn't match amongst them, but the colors were the significant piece. I needed to count how many cells had a certain color. 


I Googled and looked to see if anyone had gone through a similar situation. Yay! I came across exactly what I needed!

Count Cells by Color (link here!)

Here's the website I found. It shows 3 different methods. I chose to use Method 1. This method requires you add a script to your sheet. The link provides the script. All you have to do is copy/paste and follow the instructions.

Once you have added the script you can start using the new function "=countColoredCells(RANGE, DESTINATION COLOR)". Works similar to COUNTIF in which you need to determine the range and then the criteria (destination color). In this case, the criteria is a color. All you'll need to do is establish a color key. Add color to a cell, then use that cell address for the destination color. 

I would recommend you create a separate tab only for the color keys, then just hide it from view.

Hope you find this helpful!

Cheers!




Comments

Post a Comment

Popular posts from this blog

Hello World!

Hello World! This is how I started my first tweet some years ago and this is how I start my first public blog. I am trying to reach anyone out there that shares the same interests I have. The purpose of this blog is to share my experience as a technology trainer at an elementary school. I will be posting what has worked for me, any resources I recommend, and in general, the day to day experiences I go through in my daily life. Check back frequently and feel free to comment and share your experiences too. Let the learning begin!
Read more

Creating QR Codes

Image
Last week, fourth grade students started working on a project about Native Americans. They will create an exhibit of the different tribes that includes artifacts, pictures, posters, and QR codes. Parents will be invited, along with other teachers. The kids are so excited preparing for this special day! What is new to the kids is what is a QR code, how to create one and what to do with it. Well, QR code stands for Quick Response code. It works in a similar way as barcodes do. In this case, the image is a matrix that shows an arrangement of squares.  A QR code contains information. It could be a URL, text, a telephone number, etc. In order to "read" a QR code, you will need a QR scanner. There are apps you can download to your mobile device and easily read these codes. The iPad app we use at school is called " Scan ". There are two QR code generators I suggest using : QR Stuff In this application, you ...
Read more

Discovering Add-ons & Scripts

Image
Hi! It's back-to-school time. I hope your school year is off to a good start. As for me, it's been a very busy season. I can tell you that, amongst many other events, two major situations take place each year at my school: Open House and New Google Apps accounts. What's so special about these two? They both require a great deal of prepping and tech assistance. For instance, during Open House, the Tech Integration Team takes advantage that all the parents are present to have them register for tech orientation sessions. A couple of years ago, we'd lay out long sheets of lined paper, one for each different session, with a limited amount of lines. In the next years, we decided to take it a step further and created a Google Form. We had laptops available during each of the Open House nights and asked parents to register electronically to a tech session. While parents were submitting forms, I'd be in the background, checking numbers against the capacity we had fo...
Read more