This tutorial introduces 2 new functions from our Function by Color add-on for Google Sheets: CELLCOLOR & VALUESBYCOLORALL. Use them to sum & count cells not only by their colors but also by the common contents. Ready-made SUMIFS & COUNTIFS formulas are included ;)
If you work with colored cells in Google Sheets a lot, you may have probably tried our Function by Color add-on. Little do you know that it now has 2 more functions that expand your operations with colored cells even further: CELLCOLOR and VALUESBYCOLORALL. In this tutorial, I will introduce both functions to you and provide you with some ready-made formulas.
Before we dive into our 2 new custom functions, I'd like to briefly describe our Function by Color add-on in case you're not familiar with it.
This add-on for Google Sheets checks font and/or fill colors in the selected cells and:
There are 13 functions in total to calculate your colored cells.
Here's how it works:
For example, here in each row, I sum all items that are 'on their way' — with a blue background:
=SUM(VALUESBYCOLOR("light cornflower blue 3", "", B2:E2))
As you can see, the add-on uses the standard SUM function along with a special function inside: VALUESBYCOLOR.
VALUESBYCOLOR is our custom function.
Note. You won't find it in spreadsheets without the add-on.
It returns those cells that correspond to the colors you select in the add-on:
=VALUESBYCOLOR("light cornflower blue 3", "", B2:E2)
See? It gets only those records for each supplied item from above that are colored according to my settings. And these numbers are being calculated by one of those standard functions that I selected in the tool: SUM.
Pretty cool, huh? ;)
Well, there was a thing the add-on missed. This formula couldn't be used in SUMIFS and COUNTIFS so you still couldn't count by multiple conditions like common hue and the contents of cells at the same time. And we've been asked about it a lot!
I'm happy to tell you that we've made it possible with the latest update (October 2021)! Now Function by Color contains 2 more custom functions that will help you with that :)
2 new functions that we implemented are called VALUESBYCOLORALL and CELLCOLOR. Let's see what arguments they require and how you can use them with your data.
Note. Since the functions are custom, they are part of our Function by Color add-on. You need to have the add-on installed. Otherwise, you won't be able to use the functions and the result they return will be lost.
Tip. Watch this video or continue reading. Or do both for a better understanding ;) There's even a practice spreadsheet available at the end of the blog post ;)
This custom function requires 3 arguments:
Tip. Though the argument is required, you can totally make the function ignore fill color by entering just a pair of double quotes: ""
Tip. The argument is also required but also takes a pair of double quotes "" when you need to ignore the font color.
Have you noticed that VALUESBYCOLORALL can be easily mistaken for VALUESBYCOLOR function used by the add-on? Be careful as there's a huge difference. Take a look at this screenshot:
The formulas are written in B2 & C2 but you can peek at how they look in B8 & C8 correspondingly:
=VALUESBYCOLOR("light green 3", "", A2:A7)
=VALUESBYCOLORALL("light green 3", "", A2:A7)
Tip. The color names are taken from the Google Sheets palette:
These two functions have the same arguments and even their names are so similar!
Yet, they return different sets of data:
Even though this may seem the same to you, it makes a huge difference in combination with other functions. And this is exactly what lets you check colors along with the contents of cells with such functions as COUNTIFS or SUMIFS.
This next function is pretty easy: it checks cell colors and returns a list of color names or RGB codes (it's your choice) used in each cell. It is even called the same: CELLCOLOR.
You may not need those color names directly but you can use them in other functions, for example, as a condition.
This function also needs 3 arguments:
For example, the formula below returns the list of fill and font colors used in each cell of A2:A7:
=CELLCOLOR(A2:A7, "both", TRUE)
So how do these functions can be used with IF, SUMIFS, COUNTIFS? How do you set up your search criteria based on colors?
Let's try and use VALUESBYCOLORALL and CELLCOLOR in a few simple cases.
Here I have a short list of students passing 3 tests:
I want to mark the row with PASS in column E only if all cells in a row are green (students who passed all exams). I will use our CELLCOLOR in the IF function to check the colors and return the required string:
=IF(COUNTIF(CELLCOLOR(B2:D2,"fill",TRUE),"light green 3")=3,"PASS","")
Here's what it does:
COUNTIFS is another function that can finally count by multiple criteria even if one of them is color.
Let's suppose there are records of profits per shift and per employee:
Using our two custom functions inside COUNTIFS, I can count how many times each employee implemented the sales plan (green cells).
I will list all managers next to the table with data and enter a separate formula for each employee. I'll start with CELLCOLOR:
=COUNTIFS($A$2:$A$10,E2,CELLCOLOR($C$2:$C$10,"fill",TRUE),"light green 3")
Tip. Check the cell color using the Google Sheets palette:
Since COUNTIFS itself cannot just pick up color, I use our CELLCOLOR as a range for condition.
Remember, CELLCOLOR returns a list of colors used in each cell. When I embed it in COUNTIFS, the latter scans that list searching for all occurrences of 'light green 3'. This in combination with a name from column E gives the required result. Easy peasy :)
The same happens if you choose VALUESBYCOLORALL instead. Enter it as a range for the second condition:
=COUNTIFS($A$2:$A$10,E2,VALUESBYCOLORALL("light green 3","",$C$2:C$10),"<>")
Do you remember what VALUESBYCOLORALL returns? A list of values where all cells that meet your color requirements contain records. All other cells remain empty.
So when VALUESBYCOLORALL is put to COUNTIFS, the formula counts only those cells that are not empty: "<>" (or, in other words, correspond to the required color).
The story with SUMIFS is just like with COUNTIFS:
Note. SUMIFS doesn't take anything but a simple range as its very first argument — sum_range. If you try and embed one of our custom functions there, the formula just won't work. So keep that in mind and be sure to enter CELLCOLOR and VALUESBYCOLORALL as a criterion instead.
Here are a couple of examples.
Look at this formula:
=SUMIFS($C$2:$C$10,A$2:A$10,E2,CELLCOLOR($C$2:$C$10,"fill",TRUE),"light green 3")
The same happens with VALUESBYCOLORALL:
=SUMIFS($C$2:$C$10,$A$2:$A$10,E2,VALUESBYCOLORALL("light green 3","",$C$2:$C$10),"<>")
Hope this tutorial explains how the functions work and hint at possible ways to use them. If you still have difficulties applying them to your case, meet me in the comments section ;)
Function by Color - custom functions - examples (make a copy of the spreadsheet)
Table of contents