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.
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?
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.
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.
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.
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?
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
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?
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
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
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
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.
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 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
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?
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
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?
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
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.
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.
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!
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?
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?
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?
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?
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
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
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
It's a great ! Thank you so much from Czech republic
Fantastic! Thanks for sharing this. VBA worked very well for my set of data.
Thank you brother. It solves my daily problems.
Excellent. Well explained and much simpler than other solutions that I have found.
Thanks for taking your time to explain this!
Just what I was looking for! THANKS!!!
VBA method very helpful, Thanks.
BIG LIKE! that is exactly what I was looking for. thanks!
Glad you found the video useful!
It doesn’t work with conditional formats is there a way around that. I need a formula that’s fluid on changing conditions
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.
@@bonokul did you figure this out? I'm trying to figure out how to apply this to conditional formatting
@@WhereAmEye2187
the VBA thing is not working for me either... I typed everything as he did, but it's not working...
Excellent Sumit.. Very well explained.
Thank you.. glad you found the video useful :)
FASCINATING! 😃
Where is your color code number list? you told you already provide taht link. where is it?
VBA works perfect, also on ranges!! Many thanks for sharing!
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?
Edit the TotalCount + 1
@@jayyy_seeeee what should I write in place of that , I am also getting same issue
@@pandaandpanda Did we get an answer? I have the same issue.
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.
Brilliant! I love it. Thank you for this excellent tutorial.
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.
When I color more cells they do not reflect in the equation automatically, how can i solve this?
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.
You are a life saver 🌹
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
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?
Click F2 then Entre and you're all set
CTRL ALT F9 refreshes the whole sheet I have just found.
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
Thanx
Another great video!!! Thanks man!!!
thanks very well explained
that is rowwise. I want how to identify in a table which are randomly placed cells are randomly coloyred
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?
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
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
Thank you
Great explanation
That really helped
please share the formaula you create
in sheet 3
Does this work for conditionally formatted colors??
Thanks for sharing this... very helpful!
Used the VBA method, but I have to double click the cell that has the count to update the number.
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
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
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.
Instead of counting, can we use formula to determine True or False if any of the cell in a row is colored.
Is it possible to Average instead of Add them? How would I go about doing that?
This function does not work on conditional formatting. Is there any method we can do for conditional format color?
Thanks. Work it
Sir, what does d code line indicates total count = total count+1?
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?
Joel Soumar VBA
@@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
the VBA thing is not working for me... I typed everything as you did @TrumpExcel, but it's not working...
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?
I wonder if there is a way to count the total colored cells that has alphabetical value please?
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
very helpful, excellent
Filter in color seems to work in columns but not in rows. I don't understand that.
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
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
count or sum by font color, not background color, can work this problem with VBA ?
great video. One question. In the second example: how does ifcount know the background color of the cells on the left?
what if I dont have numbers but text?
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?
I like the second method. Can it be used for columns as well and rows?
thank you!
Can I expand the range horizontally?
How to loop the VBA?
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
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..
Instead of counting, can we use formula to determine True or False if any of the cell in a row is colored.
how to solve #name error in get.cell
thanks a lot
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.
ruclips.net/video/OrAvtXI8zVw/видео.html
You may view this video as from time 07:00
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.
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!
Thanks Puneet for your excellent work, can I save these macros as add-in.plz reply
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?
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?
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?
Hey Jonathan, did you ever get feedback on this? I am trying to count color cells based on conditional formatting. Thanks!
count cell get cell is not working pls advise to use different formula thnx
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?
I just want answer for the exact question
Hi, I wonder if you know the name in spanish of the =GetColorCount formula, since my excel has a spanish configuration. Thank you.
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?
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
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
I just tried the code you mentioned and the formula in my Excel, and it seems to be working fine.
@@trumpexcel Thanks for trying! It seems to be an issue with security settings and not the formula itself. Thanks!
i am doing this function but every time i am getting 6 .
why??? kindly explain
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
NOT WORKING
This does not work in Excel 2016.
This works in excel 2016 - > www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/
Doesnt work!!
DOESN'T WORK!!!