Custom functions to count colored cells in Google Sheets: CELLCOLOR & VALUESBYCOLORALL

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 IF, 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 now there are 2 more functions: CELLCOLOR and VALUESBYCOLORALL. They expand your operations with colored cells even further. In this tutorial, I will show you how both functions work and share some ready-made custom formulas.

• How to sum and count colored cells in Google Sheets
  • VALUESBYCOLOR function
• Extra functions of Function by Color add-on
  • VALUESBYCOLORALL function
  • Google Sheets CELLCOLOR formulas
• Sum and count cells based on color and the contents — formula examples
  • IF cell color is red, then... — Google Sheets formulas
  • COUNTIFS: count by colors & values with 1 formula
  • SUMIFS: sum cells by colors & values with 1 formula

How to sum and count colored cells in Google Sheets

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
• count cells by color (even blanks)
• finds the average/min/max values among those highlighted cells
• and more

In total, you can use 13 functions to calculate your colored cells.

Here's how it works:

1. You select the range to process.
2. Select the font and/or fill hues you want to consider and pick the function according to your task.
3. Choose to calculate records in each row/column or entire range.
4. Select cell(s) where you want to see the result.
5. Hit Insert function.

For example, in this Google Sheets table, I sum by color all those items that are 'on their way' (blue color):

=SUM(VALUESBYCOLOR("light cornflower blue 3", "", B2:E2))

Tip. There's a detailed tutorial for the add-on available here and a blog post with examples here.

As you can see, the add-on uses the standard SUM function along with a special function inside: VALUESBYCOLOR.

VALUESBYCOLOR function

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 numbers for each item from above that have a fill color from my settings. And it uses one of the standard functions that you select in the tool to tally colored cells.

Pretty cool, huh? ;)

But there was still something the add-on missed. This function didn't work with SUMIFS and COUNTIFS. So you still couldn't count by multiple conditions at the same time (like cell color and cell value). And you were constantly asking for it!

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

So these 2 new functions are 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, and you need to have it installed.

Otherwise, you won't be able to use the functions and the result they return just won't load.

Tip. Watch this video to understand how to use custom formulas to count cells based on color & value, or continue reading. Or do both for a better understanding ;) There's even a practice spreadsheet available at the end of the blog post ;)

VALUESBYCOLORALL

This custom function requires 3 arguments:

VALUESBYCOLORALL(fill_color, font_color, range)

• fill_color — hex 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 — hex 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. Look at this screenshot:

The formulas are in B2 & C2, but you can peek at how they look in B11 & C11:

=VALUESBYCOLOR("light green 3", "", A2:A10)

and

=VALUESBYCOLORALL("light green 3", "", A2:A10)

Tip. The color names are just as they are in 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 4 cells: B2:B5.
• VALUESBYCOLORALL, in its turn, returns the range of the same size as the original one (9 cells) — C2:C10. However, records in this range appear only for cells with the required fill color in column A. 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.

Google Sheets CELLCOLOR formulas

This next function is pretty easy. Google Sheets CELLCOLOR formula checks cell colors and returns a list of color names or their hex codes (it's your choice) for each cell.

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:

CELLCOLOR(range, color_source, color_name)

• 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 hex 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:A10:

=CELLCOLOR(A2:A10, "both", TRUE)

So how do you use these functions 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 cell color is red, then... — Google Sheets formulas

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:

1. CELLCOLOR(B2:D2,"fill",TRUE) returns all fill colors used in a row.
2. COUNTIF(CELLCOLOR(B2:D2,"fill",TRUE),"light green 3")=3 takes those colors and checks if 'light green 3' (in my cells) appears 3 times in a row exactly.
3. 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")

1. The first thing the formula checks is cells in column A: if there's 'Leela' (a name from E2), it takes the record into account.
2. The second thing it looks for is cells with the 'light green 3' color in column C.

   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 range of cells where only 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:

1. Take one of our custom functions: CELLCOLOR or VALUESBYCOLORALL.
2. Put it as a range that should be tested for colors.
3. 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 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")

1. CELLCOLOR gets all fill colors from C2:C10 and SUMIFS checks if any of them are 'light green 3'.
2. SUMIFS also scans A2:A10 for a name from E2 — Leela.
3. Once both conditions are met, the total from C2:C10 is ready.

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),"")

1. VALUESBYCOLORALL returns the range where only cells of the required fill color contain values. SUMIFS takes all non-empty cells into consideration.
2. SUMIFS also scans A2:A10 for 'Leela' from E2.
3. Once both conditions are met, you will get the corresponding total from C2:C10.

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 with formula examples

Function by Color - custom functions (make yourself a copy to practice)

The above is the detailed content of Custom functions to count colored cells in Google Sheets: CELLCOLOR & VALUESBYCOLORALL. For more information, please follow other related articles on the PHP Chinese website!