How To Count Colors with an Excel Function | Count Colored Cells
Вставка
- Опубліковано 28 сер 2024
- 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
🎉🎉🎉🎉🎉🥳🥳🥳🥳🥳
Thank you.
After trying chat GPT👎, Google 👎, and one other video.....YOU...you 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.
Just learn visual basic coding and you're good to go but its not easy you want more focus
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. 🙂
Thanks for the feedback. I'm glad the video helped you. 😁
a lots of year looking for this jack and you did it easy for us Bro. Thanks a lot for your time and explanation.
Brilliant, works like a charm. I found that the formulae needs to be redone every time you open a new sheet. If there is a fix, kindly share.
I'm so very grateful for this video! You have saved me hours of work. Thank you!
Glad it helped! 🙏👍
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
F9 did not work
@@user-yd1bo8ic9j not ideal but I've found that if you copy and paste colours already on the sheet to populate new colours, the count will update straight away.
love you bro. I was searching for it and found nothing useful until I saw your video. You are the best! ;)
Thank You, Very Good explanation. Looking Forward for other formulae,
One thing I would like to add, it also counts the merged cells. Where as it should give a whole count for all the cells.
I hope you would add the change and make a video
Excellent tutorial, straight to the point and easy to understand
it works for me but the function is not saved , like if i use another excel document I can't find it do you have the same problem
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?
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.
Hey you're right, the formulas don't update automatically! Where do we add that line exactly?
Thanks for this. Very clear. To the point. Helped a lot!
I just have one additional query, what can be done extra in the sheet, so that when I colour a cell as Green, the count of Green increase by +1?
Thank you! Easy and straight to the point
Do work John! This is 1x useful and easily digestible tutorial
Thanks for this valuable information... appreciated but how to make it automatically ? Each time color change then the count also change without pressing enter
I need this as well.
Very useful, couldn't find anyone else who explains this so clearly!
God no one ever explained how to activate the developer tab!! thanks i was stuck om this from 3 days...Keep posting content..You're a good teacher and explain well
You are right lol... I always like to teach objectively, but also showing all the small steps that are necessary to reach the final goal. Thanks for the positive feedback 🙏👍
You rock buddy! Straight to the point! Thank you!
This is great.....except......it doesn't seem to continue counting automatically. What I mean is, every time I put in a new entry in the coloured field, I then have to click on the count_colour function cell to bring up the edit panel and hit enter for the count to go up by one. Any ideas why that would be happening?
Perfect! U are the best 😊 One more thing I need. How can I do autorefresh outcome after I change number of colours? 😎
You just saved me so much time - THANK YOU for this!!
I'm glad you liked it! Thank you 🙏👍
I found this really helpful, thank you!
Just another UA-cam HERO! Thank you!!
Doesnt seem to pick up on formatted cell colors. Still troubleshooting
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,
I have the same question...
thank you so much! it helped me a lot
Thank you sooo much. You're a Savior...
Your tutorial has been a life saver! Thanks!
Thankyou for this, i have an issue however, i have to reapply the formula in the cell for the number to update, it doesnt update automatically. Even though I have the Calculation options set to automatic, any help?
Thank you very much for the simple explanation and valuable information.
Thank you for this information.. It worked PERFECTLY...
Good formula, but it has one problem, if the color changes in the range, the count doesn't change automatically. so we need to again refresh the formula
Is there a solution???
I agree with you
It is a problem need to be solved
It is a limitation of how Excel processes VBA functions.
Found a way. You can create a macro. For me, I have 4 cells I want to update the counts for. Record Macro > name macro > ok > click in each cell that you have the count_color formula in, once at a time, and enter at each of them. Then stop macro recording, insert a button form control and assign to the macro.
@@BCOzSportsGamer For anyone else that got confused by this, what he says is CORRECT, just make sure to click on the "=COUNT_COLOR" in the text box just underneath the ribbon
Thank you so much, nice tutorial
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.
🙏
Yes, please answer this for me too!
Thank you so much. You made it so simple and easy.😊
Glad it helped! 🙏👍
Very nice tutorial. Just wondering if it is possible to automatically refresh the count as I would like to change color let's say in one of the cells?
Thank you so much for this, so very helpful. One question, when I change a cell to the color that I'm counting, how do i get the function to automatically update? I keep having to go the the function in the bar and hit enter for it to update.
I'm very happy that the video helped you and thanks for the question.
Try to use the shortcut key F9, maybe it will be able to update the sheet without you having to click in the cell and then click out. At least, it's faster to just hit the F9.
@@JopaExcel F9 not update for me :(
Thank you so much, so happy that i dropped your vedio.. very helpful
Is it possible to count the color if the cells have multiple conditional formatting rules? It only works when I delete the number and then make the square the color but then I have no value in the cell.
When there is an character on the cell, it doesn't count.
How to get this done.?
Please help
it's work 100%, straight to the point and yea big thanks guy
thankyou, this is wonderful. however today when opening the file I get#name error. Any ideas why? it was working perfectly
Great video, what if I wanted to count the number of colored cells regardless of the unique color in a column? For example, I have a column with 8 different colored cells in it and I want a formula to just show me how many cells in that column have any color.
Thanks for the amazing tutorial. How to save this permanently in the excel. When I am closing excel it is gone. Or everytime i have save the function.
hi, thank you so much. pls update on how to write a function to count adjacent cells with same colors. if two adjacent cells(eg: C1,D1) are having same color green, need to increment count by one. if two adjacent cells are of different color green and yellow, the count doesn't increment. pls help on the same.
Wow amazing.. this worked and saved lot of time.
Glad it helped 🙏👍
So helpful, thank you!
Hello Jopa I am great fan of yours and learned lot of excel formulas by seeing your vedios now i need a help hope you would help me in our office we have a daily tracker in that we have 12 agents and works in 24/7 shifts we work on incident tickets as soon as the ticket arrives we have enter the ticket number in that sheet and change the color of the cell manually according to the time the ticket arrived for eg. if ticket came in between 8am to 9 am it will be green if it is between 9 am to 10 am then red if it is between 10 am to 11 am then purple so on so instead of changing the color manually i need a formula or a steps so based on current time when the data entered into a cell the color should change please suggest
Thanks for the tutorial, very helpful in my work 😊
Thank you!
what a useful function, thank you man
thank you. how can you count all the coloured cells? Note that these are empty cells.
hi there, thanks for this function, but I have specific question...what if I want to split the cell for multiple color (let's say I've done several things in one day and marked them differently) but I want to count them all ?
Thank you for this video. I am working within a table and my counts are correct for the current set up, but the counts are not adjusting if I make edits to the colors (such as highlighting an additional cell). Here is my formula: =COUNT_COLOR(Table1[Person],C203). Can you tell me what I am doing wrong?
Did you do the same as the video? Maybe is the formulas/functions in your spreadsheet are not automatically update. Check this, please. Maybe it can solve the problem.
To check Calculation Settings: Click on the "Formulas" tab in the Excel ribbon and then, "Calculation". Calculation mode needs to be set as Automatic.
Hope this can help you.
I have the same issue and the calculate mode is set as automatic @@JopaExcel , do you know what could be wrong?
I did the same as the video.
Thank you for the video. The function works well when the cells that need to be analyzed are in the same Excel book in which I am making the function run. However, when I try to count the cells using a link to a different excel book, it only works when the referenced spreadsheet is open. If I refresh the excel in which the "COUNT_COLOR" function is being executed while the source excels are closed, I receive a "VALUE" error. I have verified the links, and they are not broken. Do you know how to fix the function so that it also works with references to other excel books?
PERFECT!!!! THANK YOU SO MUCH FOR YOUR TUTORIAL
Glad it helped! Thank you 😁🙏👍
Exactly what i needed thanks!
Glad it helped! 🙏👍
How do you select a range of cells that are separate?
For example, I want to select C1, C4 and C6 but it gives me an error.
I need to thank you. This is perfect and I needed it
Glad it helped you! 🙏👍
Why mine comes as #NAME?
This is amazing, thank you so much!
Which one we have to copy and paste
Excellent tutorial, thank you!
I keep getting the message that there is a problem with the formula, when I add the comma and then want to select the cell with the color....
hello! This was a great solution indeed! But is there a way to count cells color for conditionally formatted cells?
I was going to ask the same question. It does not work for cells which changed colour using conditional formatting. Could you please help?
thanks for sharing...
Is there a way to refresh the count? Seems I have to go into the cell then press enter.
Thank you so much. This helped me alot.
Not working... I get the new function, but then trying to select the cell for the colors, I get an error.
This video was very useful. Thank you!
Doesn't work, i permanently get the error that if i put an "=" at the start i need some kind of result or whatever
Same. Sad.
Does not work with conditional rules.
Second sheet random boxes are coming counted for me but not the first one shown..
not working for color that applied from conditional formatting
Thank you so much! Easy and quick!
Glad it was useful! 🙏👍
Thank you so much for this!
Hi, but what is the functuon if we want to summ/count colors between some dates there? F.e, we need to find sum or count between 19 jul and 27 jul?
Fantastic! Thanks.
Thank you for this tutorial. Very helpful indeed!
hi, great tutorial! can you do that with font colours/format?
I have created the count color formula as per user instruction but the formula is not found in other file, can you help me to add that formula in whole excel file
Great! It works. Thanks!
Best, but why when I add new cells with the same colors the count not changed automatically? its stay with count I counted at first
YOU SAVED ME THANK YOU!
Glad it was useful. Thank you 🙏👍
not working while using conditional formatting, can you help with that?
hello! this works amazing but is there a way to count cells color for conditionally formatted cells?
Agreed!!!! Show me how to do that and I will be ecstatic!!!!!!
If I change a color should the total auto update?
Good idea but there’s one problem
I don’t have the developer tab up on my screen ….
Excellent !!!
Error "Compile error: Expected: identifier" appeared, what can I do?
hi I added the formulation in macro but it doesn't work and massage formulation has a problem, I am using Mac , is there any limitation for the excels which using in Mac?
Thanks exactly looking for that
Your welcome. Glad it was useful 🙏👍
Hi, it does not work on cells with conditional formatting. Is there a way out? Thanks!
Hey had a question - I created some conditional formatting to mark some cells if they were blank or not. That part works fine. The colors go brown if they're blank and blue if they have text. But the issue is this code is NOT picking up my conditionally formatted cells for some reason. Any guidance on troubleshooting?
I have this issue too!
it works for me but the function is not saved , like if i use another excel document I can't find it
Code works great if one group of data is selected. Im having an issue when I want to select multiple seperate columns at once. (ie:=COUNT_COLOR(AN2:AN41,AI2:AI55,BI10) where im slecting 2 seperate columns of data. Gives me "value is wrong data type" error. Solutions?
I just tried this to count the colors in a row and it is not working for me. The formula is working but it is not counting the cells that have a color in them. My example formula is =count_color(B2:H2,J2) is there something I am missing? I have 3 cells that match the color of j2 but it shows up as 0.
Thanks a lot man!
Happy to help!
will it work on any version of excel?
Loveeee❤🎉🎉🎉 thank You so much!
Thank you very much!
Unfortunately excel is telling me that formula is incorrect :(