How To Count Colors with an Excel Function | Count Colored Cells
HTML-код
- Опубликовано: 22 авг 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
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.
🎉🎉🎉🎉🎉🥳🥳🥳🥳🥳
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, nice 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.
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?
thank you so much! it helped me a lot
a lots of year looking for this jack and you did it easy for us Bro. Thanks a lot for your time and explanation.
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! Easy and straight to the point
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. 😁
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...
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.
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?
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
I'm so very grateful for this video! You have saved me hours of work. Thank you!
Glad it helped! 🙏👍
Do work John! This is 1x useful and easily digestible tutorial
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?
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
You rock buddy! Straight to the point! Thank you!
Very useful, couldn't find anyone else who explains this so clearly!
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!
You just saved me so much time - THANK YOU for this!!
I'm glad you liked it! Thank you 🙏👍
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?
love you bro. I was searching for it and found nothing useful until I saw your video. You are the best! ;)
Perfect! U are the best 😊 One more thing I need. How can I do autorefresh outcome after I change number of colours? 😎
Your tutorial has been a life saver! Thanks!
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
Just another RUclips HERO! Thank you!!
Doesnt seem to pick up on formatted cell colors. Still troubleshooting
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. You made it so simple and easy.😊
Glad it helped! 🙏👍
Thank you very much for the simple explanation and valuable information.
Thank you sooo much. You're a Savior...
thanks for sharing...
So helpful, thank you!
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?
When there is an character on the cell, it doesn't count.
How to get this done.?
Please help
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.
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.
thankyou, this is wonderful. however today when opening the file I get#name error. Any ideas why? it was working perfectly
Thanks for the tutorial, very helpful in my work 😊
Thank you!
it's work 100%, straight to the point and yea big thanks guy
Thank you so much, so happy that i dropped your vedio.. very helpful
Thank you for this information.. It worked 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.
Fantastic! Thanks.
Exactly what i needed thanks!
Glad it helped! 🙏👍
PERFECT!!!! THANK YOU SO MUCH FOR YOUR TUTORIAL
Glad it helped! Thank you 😁🙏👍
Thank you so much for this!
what a useful function, thank you man
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.
Which one we have to copy and paste
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.
This is amazing, thank you so much!
thank you. how can you count all the coloured cells? Note that these are empty cells.
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
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?
Excellent tutorial, thank you!
I need to thank you. This is perfect and I needed it
Glad it helped you! 🙏👍
Thank you for this tutorial. Very helpful indeed!
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 ?
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.
Thank you so much! Easy and quick!
Glad it was useful! 🙏👍
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!
This video was very useful. Thank you!
Thank you so much. This helped me alot.
Is there a way to refresh the count? Seems I have to go into the cell then press enter.
Thanks a lot man!
Happy to help!
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?
not working for color that applied from conditional formatting
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.
Excellent !!!
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 🙏👍
If I change a color should the total auto update?
Wow amazing.. this worked and saved lot of time.
Glad it helped 🙏👍
hi, great tutorial! can you do that with font colours/format?
Why mine comes as #NAME?
YOU SAVED ME THANK YOU!
Glad it was useful. Thank you 🙏👍
Thank you very much!
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?
thank you so much
Second sheet random boxes are coming counted for me but not the first one shown..
Loveeee❤🎉🎉🎉 thank You so much!
Thanks exactly looking for that
Your welcome. Glad it was useful 🙏👍
not working while using conditional formatting, can you help with that?
Thank you for the code
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!!!!!!
Not working... I get the new function, but then trying to select the cell for the colors, I get an error.
Hi, it does not work on cells with conditional formatting. Is there a way out? Thanks!
will it work on any version of excel?
AMAZING Thank you so much!!
Thanks 🙏👍
Thank You!
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
Thanks a lot genius
thank you!
Amazingly good
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!
Thank you boss !!
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?