Video: How to use CELLCOLOR & VALUESBYCOLORALL in Google Sheets

Function by Color add-on supplies your Google Sheets with 2 new custom functions: CELLCOLOR & VALUESBYCOLORALL. Watch how to use them for your colored cells in any of your own formulas.

Tip. Click here to make your copy of the file from this video and follow the tutorial along.

Video transcript: Function by Color add-on for Google Sheets

If you use our Function by Color add-on for Google Sheets, you most likely find it useful to sum and count colored cells.

Upon your requests, we've added 2 new functions that will let you consider cell colors in any of your own formulas.

For example, you will count cells not only by their colors but also by their contents and even the related values from another column.

These functions are: CELLCOLOR & VALUESBYCOLORALL

  • CELLCOLOR checks fill, font, or both colors in all selected cells, and returns the names of these colors for each cell.
  • VALUESBYCOLORALL returns the range of the same length as your source data, but only cells with the required fill & font colors contain values.

CELLCOLOR & VALUESBYCOLORALL
Let's see how you will benefit from these 2 extra functions in spreadsheets.

New custom functions in use

Here I have a sample of weekly profits earned by different managers on different shifts.

Now, since the functions are custom-made, you must have either Function by Color or Power Tools installed. No need to run the add-ons though — just pick a cell, enter an equal sign, and start typing the name of the function: CELLCOLOR.

CELLCOLOR syntax

This function needs 3 arguments:

  1. Start with a range that you want to scan for colors.
  2. Then in double-quotes define whether to get fill color, font color, or both at the same time.
  3. Last but not least, is a way to have those color names. Enter FALSE to get them as the RGB codes and TRUE for the color names themselves, such as 'red'.

I close the brackets, hit Enter, and I get the name of the fill color used in each cell of column C.
CELLCOLOR syntax & result

VALUESBYCOLORALL syntax

Moving on to VALUESBYCOLORALL.

When you start entering its name, you'll see VALUESBYCOLOR first. That's the function used by the add-on itself.

You need the second one — VALUESBYCOLORALL.

There are 3 arguments here as well:

  1. The first is the name or the RGB code of the required fill color.
    Tip. You can check it if you select a cell of interest, open the Fill color tool, and hover your mouse over the selected color. There, 'light green 3'. That's what I enter in double quotes.
  2. The same goes for the second argument — font_color. Since all my numbers are black and the font color doesn't really matter, I enter empty double quotes to ignore it.
  3. Lastly, I select the range to process, close the brackets, and hit Enter.

The function takes the range of the same size as it handles, but returns values only from those positions that correspond to the colors I set.
CELLCOLOR syntax & result
So how do these functions work in your own formulas?

CELLCOLOR formula examples (in SUMIFS/COUNTIFS)

Let's try and sum only Leela's profits that are colored green.

I actually have these formulas ready, so let me just unhide them:

  • Here: in I2 I embedded CELLCOLOR in SUMIFS.

    It sums numbers from column C only if the same row in column B contains 'Leela' and if cells in column C are colored green.

    That's where I use the CELLCOLOR function — it becomes the criteria range for SUMIFS. It gets all fill colors from column C (just like I showed you earlier in column E) and SUMIFS checks which of them are light green 3.
    CELLCOLOR with SUMIFS

  • The same applies to COUNTIFS in J2. Our CELLCOLOR works as one of the criteria ranges so you could check cells for their colors.

VALUESBYCOLORALL formula examples (in SUMIFS/COUNTIFS)

As for VALUESBYCOLORALL in I3 and J3, it is used in the exact same way — as a criteria range.

However, since it returns records for the required color (like here in column F), I modify the last criteria: sum only non-blank cells returned by VALUESBYCOLORALL.
VALUESBYCOLORALL with SUMIFS
These are just a couple of ways to use these custom functions.

Install Function by Color or Power Tools to your Google Sheets to process colored cells easily on a daily basis.

And if you have any questions, please contact us — we'll be happy to help.

Spreadsheet to practice along

Custom functions to work with colors (make your copy of the spreadsheet)

You may also be interested in