Welcome to our tutorial on how to use Excel COUNTIF and SUMIF Based On Cell Color.
⬇️Download DataFX free function library:
excelkid.com/add-ins/
🎓 LEARN MORE in my Excel tutorials: excelkid.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
🎁 SHARE this video and spread the Excel love.
Chapters:
00:00 Intro
00:09 COUNTIF based on cell color
00:46 Limitations of the GET.CELL function
01:05 COUNTBYCOLOR and SUMBYCOLOR functions
To count or sum cells by color in Excel you can use different methods:
1. Using the GET.CELL function
2. Using the COUNT BY COLOR and the SUM BY COLOR functions.
In the example, I want to use countif based on this color. First, create a named range. Go to the Formulas Tab and click on define name.
Add a name, for example, ColorCode. In cell D4 apply the colorcode function and copy the formula down. Now you can count the number of colored cells using the COUNTIF formula. This formula will count cells in the ColorIndex column if they have a matching code. In this example, it counts all the six values corresponding to the blue color. And here is the sum of the blue cells.
The main problem is that the GET CELL function can identify only 56 different colors. Now, I'll adjust the shade of blue, and then apply the formula. Unfortunately, it still returns the same color code, so the calculation will be incorrect.
So, we need to find a better solution. Take a closer look at the COUNTBYCOLOR function, it is a lightweight solution. The function is a part of our free library. Type the function and add the first argument, the cell color, and then use the range as a second argument. Press Enter to get the result.
The SUMBYCOLOR function uses the same logic. The greatest advantage of the new functions is that they recognize all the 16 million existing colors in Excel.