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.
Sum and count colored cells with Function by Color
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:
- sums numbers with a common hue
- counts colored cells and even blanks
- finds the average/min/max values among those highlighted cells
- and more
There are 13 functions in total to calculate your colored cells.
Here's how it works:
- You select the range to process.
- Select the font and/or fill hues you want to consider and pick the function according to your task.
- Choose to calculate records in each row/column or entire range.
- Select cell(s) where you want to see the result.
- Hit Insert function.
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 :)
Extra functions of Function by Color
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:
- fill_color — RGB code or color name (per Google Sheets color palette) for a background color.
Tip. Though the argument is required, you can totally make the function ignore fill color by entering just a pair of double quotes: ""
- font_color — RGB code or color name (per Google Sheets color palette) for a text color.
Tip. The argument is also required but also takes a pair of double quotes "" when you need to ignore the font color.
- range — nothing fancy here, just a range of cells that you want to process.
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:
- VALUESBYCOLOR returns the list of only those records that appear with a green fill color in column A. The outcome of this formula takes only 3 cells: B2:B4.
- VALUESBYCOLORALL, in its turn, returns the range of the same size as the original one (6 cells) — C2:C7. But the cells in this range contain records only if the corresponding cell in column A has the required fill color. Other cells remain empty.
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:
- range — those cells that you want to check for colors.
- color_source — tells the function where to look at:
- use the word "fill" in double quotes to check for background colors
- "font" — for text colors
- "both" — for both fill and text colors
- color_name — your way of telling what kind of name to return:
- TRUE gets you the names that you see in a Google Sheets palette, e.g. red or dark blue 1
- FALSE gets RGB codes of the colors, e.g. #ff0000 or #3d85c6
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?
Sum and count cells by color and the contents — formula examples
Let's try and use VALUESBYCOLORALL and CELLCOLOR in a few simple cases.
IF color, then...
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:
- CELLCOLOR(B2:D2,"fill",TRUE) returns all fill colors used in a row.
- COUNTIF(CELLCOLOR(B2:D2,"fill",TRUE),"light green 3")=3 takes those colors and checks if 'light green 3' (which I use in my cells) appears 3 times in a row exactly.
- If so, IF returns 'PASS', otherwise, the cell remains empty.
COUNTIFS: count by colors & values with 1 formula
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).
Example 1. COUNTIFS + CELLCOLOR
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")
- The first thing the formula checks is column A: if there's 'Leela' (a name from E2), it takes the record into account.
- The second thing I need to check is if cells in column C are colored 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 :)
Example 2. COUNTIFS + VALUESBYCOLORALL
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).
SUMIFS: sum cells by colors & values with 1 formula
The story with SUMIFS is just like with COUNTIFS:
- Take one of our custom functions: CELLCOLOR or VALUESBYCOLORALL.
- Put it as a range that should be tested for colors.
- Enter the condition depending on the function you selected: the name of the color for CELLCOLOR and "not empty" ("<>") for VALUESBYCOLORALL.
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.
Example 1. SUMIFS + CELLCOLOR
Look at this formula:
=SUMIFS($C$2:$C$10,A$2:A$10,E2,CELLCOLOR($C$2:$C$10,"fill",TRUE),"light green 3")
- CELLCOLOR gets all fill colors from C2:C10 and SUMIFS checks if any of them are 'light green 3'.
- SUMIFS also scans A2:A10 for a name from E2 — Leela.
- Once both conditions are met, the amount from C2:C10 is added to the total.
Example 2. SUMIFS + VALUESBYCOLORALL
The same happens with VALUESBYCOLORALL:
=SUMIFS($C$2:$C$10,$A$2:$A$10,E2,VALUESBYCOLORALL("light green 3","",$C$2:$C$10),"<>")
- VALUESBYCOLORALL returns the range where only cells of the required fill color contain values. SUMIFS takes all non-empty cells into consideration.
- SUMIFS also scans A2:A10 for 'Leela' from E2.
- Once both conditions are met, the corresponding amount from C2:C10 is being totalled.
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 ;)
Spreadsheet to practice along
Function by Color - custom functions - examples (make a copy of the spreadsheet)