Count Colored Cells in Excel (using Formula or VBA)

[@jansykora6390]

It's a great ! Thank you so much from Czech republic

[@sepidehnourian6404]

Fantastic! Thanks for sharing this. VBA worked very well for my set of data.

[@agapheputrasusilo741]

Thank you brother. It solves my daily problems.

[@lukec.9819]

Just what I was looking for! THANKS!!!

[@bsnunnery]

Super helpful!!! Thank you so much!

[@paolagutierrez9086]

Thanks for taking your time to explain this!

[@garys2187]

Excellent. Well explained and much simpler than other solutions that I have found.

[@redhaakhund1271]

I have below code and its not working as to be blinking cell. Plz advise. Many thanks sir Sub Blinking_Status() Dim BlinkingCells As Range For Each BlinkingCells In Range("VacationList_Tbl[Status]") If BlinkingCells = "On Leave" Then BlinkingCells.Interior.color = 255 'Red ElseIf BlinkingCells = "On Duty" Then BlinkingCells.Interior.color = 14395790 'Blue ElseIf BlinkingCells = "Waiting" Then BlinkingCells.Interior.color = 65535 'Yellow End If Next BlinkingCells Application.OnTime Now + TimeValue("00:00:06"), "Blinking_Status", , True End Sub

[@maggiewhitaker9655]

VBA method very helpful, Thanks.

[@Kingmaker-cr4wj]

Excellent Sumit.. Very well explained.

[@trumpexcel]

Thank you.. glad you found the video useful :)

[@Wael_Shabo]

BIG LIKE! that is exactly what I was looking for. thanks!

[@trumpexcel]

Glad you found the video useful!

[@jerryr536]

It doesn’t work with conditional formats is there a way around that. I need a formula that’s fluid on changing conditions

[@bonokul]

Hello Jerry, did you ever get an answer to your question regarding the work around with conditional formatted colors. I am working on a project trying to use the technique above, but for colored cells based on conditional formatting. Thanks.

[@WhereAmEye2187]

@@bonokul did you figure this out? I'm trying to figure out how to apply this to conditional formatting

[@tiborboth746]

@@WhereAmEye2187 the VBA thing is not working for me either... I typed everything as he did, but it's not working...

[@OzduSoleilDATA]

FASCINATING! 😃

[@dbblagent6865]

Where is your color code number list? you told you already provide taht link. where is it?

[@keokethomas839]

Hi, thanks for the explanation. I decided to use the VBA method, however I am consistently getting '0' as the value, even though I have several of each colour in the range. I also have blank(white-no colour) cells in the range as well, does this make a difference? Are you able to assist or advise why it doesnt seem to be able to count the colours in the range?

[@jayyy_seeeee]

Edit the TotalCount + 1

[@pandashashwat]

@@jayyy_seeeee what should I write in place of that , I am also getting same issue

[@ttalvord]

@@pandashashwat Did we get an answer? I have the same issue.

[@metububu]

Most probably it is not the same color as you select, It could be slightly different color "RGB" and you could not realize it. Be sure you use same color.

[@ricksotelo9144]

I how about if wanted the function to automatically change the count if I decided to change the colors on the cells manually. Would this function allow me to do this?

[@36cycle]

VBA works perfect, also on ranges!! Many thanks for sharing!

[@mhammad3492]

You are a life saver 🌹

[@AjayKumarparmar]

nice video Trump Excel. I also teach people on excel and vba and this video was really very informative ,the second trick. keep up the good job

[@VirenderSingh-w5c]

please share the formaula you create in sheet 3

[@ventjemazzel8822]

Another great video!!! Thanks man!!!

[@savethedatestickers5725]

Brilliant! I love it. Thank you for this excellent tutorial.

[@njemmison]

Does this work for conditionally formatted colors??

[@ganeshoptom]

This function does not work on conditional formatting. Is there any method we can do for conditional format color?

[@FPLMikkel]

This works perfect, except the cells containing the GetColorCount is not updating automatically. They did so the first time when I made the spreadsheet, but as I am reopening it, it will not update automatically. Any ideas?

[@mariaheart8259]

Click F2 then Entre and you're all set

[@JM-rd5eh]

CTRL ALT F9 refreshes the whole sheet I have just found.

[@justWithRight]

When I color more cells they do not reflect in the equation automatically, how can i solve this?

[@bnkwupt]

I'm encountering the same issue. I've found that you have to manually recalculate the colored cells by clicking in your GetColorCount cells, then click in the formula bar and press enter. It will recalculate the totals. This applies to his third scenario for using a custom function.

[@tiborboth746]

the VBA thing is not working for me... I typed everything as you did @TrumpExcel, but it's not working...

[@eagle3498]

Hi there, i applied your method of count using custom function but m getting "zero" in cells where they should give me total number of color cells. Can you help.pls. thx

[@PierreBellefeuille_Muse]

Thanks for this tutorial. I tried the third method. It works, but the count is not auto updated if I add at the end another coloured cell into a column. The only way at this time to update the result is to select the cell where the count should appear, then go at the end of the equation into the equation bar and type Enter. Do you have any idea why the count cell isn't auto updating the result with a specific column when I add to this column a new coloured cell? I saw down here that many are facing the same issue.

[@prees2509]

that is rowwise. I want how to identify in a table which are randomly placed cells are randomly coloyred

[@archanaaa2514]

Sir, what does d code line indicates total count = total count+1?

[@anhnhatnguyet4628]

count or sum by font color, not background color, can work this problem with VBA ?

[@arpadcsorba2053]

Used the VBA method, but I have to double click the cell that has the count to update the number.

[@rubicxcibur]

Filter in color seems to work in columns but not in rows. I don't understand that.

[@jeremydrennen6967]

Is it possible to Average instead of Add them? How would I go about doing that?

[@abj9121]

after you select your cells its hard to understand what to type: F or F4. it does not matter cos none of the options work and im confused about how to close those brakets

[@cdafonseca1]

thanks very well explained

[@narendramohan85]

Dear I have taken A B C D and using same formula but showing 0. And if i am taking Number instead of A B C D that working , can u help

[@someone2879]

Thank you Great explanation That really helped

[@viqq183]

How to loop the VBA?

[@gonzalopineda3185]

Thanks for sharing this... very helpful!

[@LiquidSnake1988]

how about I want all blue to be in the in order in other sheet and In that sheet if I modify it or replace its text it should reflect to the original sheet? how? thanks

[@lynner5027]

This is great, but anyway it can work for decimals? I have never used VBA before and this seems to only work for whole numbers

[@joelsoumar7819]

Thanks This was helpful in creating a time-sheet to keep track of vacation and sick days used. The only problem I am having is I must reenter the formula for it to total anytime a background color in the range is changed. Is there a way to make this so that it auto updates when I change the color?

[@dempseyroll96]

Joel Soumar VBA

[@bethsansone3921]

@@dempseyroll96 I used VBA and it didn't update if I change the color after entering the formula initially. I have to go to the cell with the formula, click on the formula bar, and hit enter to update it

[@leeschofield6071]

If you need to SUM the CONTENTS of the coloured cells, I used the 3rd VBA method simply change this code line TotalCount = TotalCount + 1 to this... TotalSum = TotalSum + rcell.Value

[@m71544232]

I wonder if there is a way to count the total colored cells that has alphabetical value please?

[@latinourbano]

what if I dont have numbers but text?

[@SmartcadSoftwareSolutions]

Can I expand the range horizontally?

[@erickmaidana8280]

how to solve #name error in get.cell thanks a lot

[@falekavamahe4631]

My cells are under conditional formatting and it counts each cell even if it is not the color I nominated Is there a way around this? Thanks

[@sirshiirev804]

very helpful, excellent

[@joshuarosario7223]

Hi Sumit, this tutorial was very helpful for me, but after applying the getcolor macro i have to repeatedly refresh the formula. Is there any other way around it, i have also tried closing the file and reopening it and still remains the same until i go to each cell and refresh the formula.

[@ashvinkalleejaisriram]

kzbin.info/www/bejne/haOkp6eOfp2tjNk You may view this video as from time 07:00

[@katjanartey5720]

Very useful! VBA works perfectly on the overall report but when I use a filter it then does not adjust the cells counted based on the filter selection. How can this be solved?

[@sajadmk6618]

Respected Sir, I've a doubt...if cell colour is changed then formula will not execute automatically...is there any vba code to do that..

[@sergiocordova3628]

Hello Trump Excel. Thank you for the informative video. I attempted to use the VB Editor under Office 360 for Mac version and could it would not RUN PROGRAM due to an error on the TotalCount = Total Count + 1 line. Would you have another option?

[@zaheerali5042]

Hi Sumit it's awesome, but it's not working in conditional formatting. can you share any other formula which will work on conditional formatting not Macro.

[@suja3443]

Instead of counting, can we use formula to determine True or False if any of the cell in a row is colored.

[@theostravel]

Thank you so much. I will use the VBA method. Is there a way to keep a running count of cell color? I use a call sheet, and the way to keep count of daily call counts for the week is by highlighting the cells, and the following week re-use the sheet. How can I have the results update by the cells highlighted in say Yellow, and then cleared back to zero when I remove the yellow highlight - then change based on the next weeks usage?

[@cyunglam1]

Thanks for sharing it! I copied and pasted your Macros formula and did exactly what you showed but it always gives a error message as "#NAME?". Do you know why? Thanks!

[@Sinatra_67]

Hi - I have tried using 'get cell' all day but keep getting an error when I use the named cell to count. The error comes back as name, any suggestions?

[@derrylslh]

I have a question: How to count numbers with combination? For example: 1234 have 24 permutation, so if cell 1 is 2341and cell 2 is 3412 the result count is 2, is there a formula for this kind if count, please help thanks.

[@DEVIDAS915]

Thanks. Work it

[@jacobprestia248]

Hi is there a formula or macro to say "yes" or "no" in a cell based on a colour of another cell? for example if i have a cell coloured in green a text comes up in the cell next to it that says "yes" if the cell is green or "no" if the cell is not green?

[@suja3443]

I just want answer for the exact question

[@franciscoramosberninzon8623]

Hi, I wonder if you know the name in spanish of the =GetColorCount formula, since my excel has a spanish configuration. Thank you.

[@kirandeepchoudhry9232]

Thanks Puneet for your excellent work, can I save these macros as add-in.plz reply

[@1989acha]

great video. One question. In the second example: how does ifcount know the background color of the cells on the left?

[@DianaEvans1228]

I like the second method. Can it be used for columns as well and rows?

[@llambproduxions]

thank you!

[@TehDectator]

Hi, Can you please give me the formula for the below question: Using a formula, find the value of in the cell which is highlighted in green without directly referencing the cell

[@shaukatsamad2510]

count cell get cell is not working pls advise to use different formula thnx

[@gazaille89]

What if the cell has been turned to a Color with Conditional Formatting? Is it possible to count those cells just as you did in the third example?

[@taraelliott8031]

Hey Jonathan, did you ever get feedback on this? I am trying to count color cells based on conditional formatting. Thanks!

[@alializadeh8195]

Thanx

[@suja3443]

Instead of counting, can we use formula to determine True or False if any of the cell in a row is colored.

[@svikram78]

i am doing this function but every time i am getting 6 . why??? kindly explain

[@stanisfine]

This was amazing and very helpful. I have a particular project where I will be leveraging this, however its a little more convoluted. I have a spreadsheet of seating arrangements in a large classroom. There is a grid in the spreadsheet that defines the different age range and scattered in the spreadsheet are student names with colors that fit their age. My goal is to sort the information in a simple column that would say if student in cell B5 is color green and green is defined as age range between 18-19 in cell D4, then sort this data by making G1=name and H1=age range. Is this possible?

[@emotionbyhasan8135]

Deer Bro Pls Help Me Exel Cell Color Count Formula In Ur Exel GetColorCount Formula , I will Send To ue email my exel sheet , i will try but not work , pls help us

[@rvde3749]

Function GetColorCount(CountRange As RANGE, CountColor As RANGE) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

[@rvde3749]

That is the function i typed over. When I am trying to use the formula in excel I get the standard erro 'there's a problem with your formula' . It happens after I click Enter. Can someone spot the mistake? It is my first time using a function with VBA =GetColorCount(A14:$AC$138,A158) First is the range, and A158 is just an empty cell in the colour I want to count. I hope someone can help

[@trumpexcel]

I just tried the code you mentioned and the formula in my Excel, and it seems to be working fine.

[@rvde3749]

@@trumpexcel Thanks for trying! It seems to be an issue with security settings and not the formula itself. Thanks!

[@larki0917]

This does not work in Excel 2016.

[@andrejvlasenko5135]

This works in excel 2016 - > www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/

[@muhammadsaqib2961]

NOT WORKING

[@SrinivasanNSamskritam]

Doesnt work!!

[@JohnMacaulay.Accountant]

DOESN'T WORK!!!