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

Just what I was looking for! THANKS!!!

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

Thank you brother. It solves my daily problems.

Thanks for taking your time to explain this!

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

Thanks for sharing this... very helpful!

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

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

VBA method very helpful, Thanks.

Thank you Great explanation That really helped

Another great video!!! Thanks man!!!

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

@trumpexcel

3 жыл бұрын

Glad you found the video useful!

thanks very well explained

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

FASCINATING! 😃

very helpful, excellent

thank you!

Excellent Sumit.. Very well explained.

@trumpexcel

Жыл бұрын

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

You are a life saver 🌹

Thanks. Work it

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

Thanx

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

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

@bonokul

4 жыл бұрын

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.

@JimmyHernandez2187

3 жыл бұрын

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

@tiborboth746

3 жыл бұрын

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

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

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?

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

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

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?

@j_el_cee1299

2 жыл бұрын

Edit the TotalCount + 1

@pandaandpanda

2 жыл бұрын

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

@ttalvord

2 жыл бұрын

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

@metubup

8 ай бұрын

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.

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?

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?

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

@bnkwupt

3 жыл бұрын

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.

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.

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

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?

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!

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

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

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

4 жыл бұрын

Click F2 then Entre and you're all set

@JM-rd5eh

4 жыл бұрын

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

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

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

please share the formaula you create in sheet 3

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

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

4 жыл бұрын

Joel Soumar VBA

@bethsansone3921

4 жыл бұрын

@@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

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

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?

what if I dont have numbers but text?

Does this work for conditionally formatted colors??

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?

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

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

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

Can I expand the range horizontally?

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

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

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

6 жыл бұрын

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

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

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

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

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.

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

How to loop the VBA?

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

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.

@ashvinkallee143

2 жыл бұрын

kzread.info/dash/bejne/gaZ12NaReZvghto.html You may view this video as from time 07:00

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..

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

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

8 жыл бұрын

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

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

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

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 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

6 жыл бұрын

I just want answer for the exact question

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

This does not work in Excel 2016.

@andrejvlasenko5135

5 жыл бұрын

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

NOT WORKING

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

2 ай бұрын

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

2 ай бұрын

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

@rvde3749

2 ай бұрын

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

Doesnt work!!

DOESN'T WORK!!!