How To Count Colors with an Excel Function | Count Colored Cells

In this Excel video tutorial, I'll show you how to make an Excel function to count colors in Excel.

If you have a table, a spreadsheet, where you colored your cells, you can count the colors with this formula that I'm going to show you.

So, for example, in an accounts payable spreadsheet, you painted all overdue accounts yellow and now you need to know how many overdue accounts, yellow cells, you have in your table. With this color counting function in Excel, you will be able to do this math.

To create this function in Excel, we will use Excel's Visual Basic Application or VBA.

Function COUNT_COLOR(RANGE As Range, COLOR As Range)
Dim COLORC As Integer
Dim COUNTT As Integer
COLORC = COLOR.Interior.ColorIndex
Set IC = RANGE
For Each IC In RANGE
If IC.Interior.ColorIndex = COLORC Then
COUNTT = COUNTT + 1
End If
Next IC
COUNT_COLOR = COUNTT
End Function

#JopaExcel #Dashboard #Excel

[Lecramo]

Thank you so much for this guide.
Here are some tips for other viewers that worked for me:

1.) using "Application.Volatile" as second line of code, you can refresh the counting by pressing F9
2.) using "COLOR.Interior.Color" instead of "COLOR.Interior.ColorIndex" should lead to comparison of the real RGB values. Otherwise different colors may have the same index.

3.) My European folks, use ";" instead of ", " to separate the range and the reference cells, as your excel probably expects you to use ;

[meowmeow]

🎉🎉🎉🎉🎉🥳🥳🥳🥳🥳
Thank you.

After trying chat GPT👎, Google 👎, and one other are the first person/video to show me a solution that works. Thank you.

Chapt gpt's not taking anyone's job in excel this year. Yah, it's fancy, but who cares if it consistently spits out things that don't work.

[franciscorosario]

a lots of year looking for this jack and you did it easy for us Bro. Thanks a lot for your time and explanation.

[lizgabbett]

I'm so very grateful for this video! You have saved me hours of work. Thank you!

[suniyem]

Thanks for this valuable information... appreciated but how to make it automatically ? Each time color change then the count also change without pressing enter

[florencetan]

Helpful script and simple tutorial to understand. Thanks!

[robertrussi]

Excellent tutorial, straight to the point and easy to understand

[billycen]

Do work John! This is 1x useful and easily digestible tutorial

[JammJammli]

Hi i've use the method you show and it works🎉🎉🎉
But 1 issue i found is after i save the file as macro-enable worksheet the formulas did not automatically update i have to double click the formular cell in order for it to recalculated

[HcV]

You rock buddy! Straight to the point! Thank you!

[isaacgarciarojas]

Thank you for the video! I would just add a line below the function declaration that states: Application.Volatile so the values returned by the function update as the spreadsheet changes.

[richardkavanagh]

Thank you for this. How do I get it to refresh automatically. It seems at the moment if I change a cell colour the count does not change,

[thrpersonalaccount]

Thank you so much! There isn't much out there for Macs, so I didn't even know where to start (I've never created a UDF or Macro before in Excel). You made it so straight-forward. 🙂

[mif]

Thank you! Easy and straight to the point

[irshduck]

Just another YouTube HERO! Thank you!!

[SkiShorts]

Very useful, couldn't find anyone else who explains this so clearly!

[kristycarlson]

You just saved me so much time - THANK YOU for this!!

[edygless]

Hello!
Thank you for your help!!! This video helps me to solve a problem. But, How can I do the function to automatically update the count?

[razvancarp]

love you bro. I was searching for it and found nothing useful until I saw your video. You are the best! ;)

[AA-njni]

It doesn't work for my table of conditionally formatted colored cells. So i have a large set of number which are highlighted green whenever they are equal a certain number. I wanted the formula to count the highlighted conditionally highlighted cells when they change. This only works when the cells are manually colored.. do you know how to solve this for me please.

🙏