How to count colored cells in Excel

[@BarbHendersonconsulting]

Thank you so much for watching my video. If you have any suggestions for future topics please leave them in the comments.

[@sanjeebphotography1214]

Thank you Barb for this vedio. I have one query. While entered the code I am getting compile error pop-up that is expected:Than or GoTo can you please suggest.

[@tiamat1628]

I dont have the countcolorif function.. How can I get it on my Excel?

[@hiteshbhilai2010]

@@tiamat1628 you would need to add the module in VBA for this new function as she had mentioned in description Code Function CountColorIf(Samplecell As Range, bArea As Range) As Long Dim bAreacell As Range Dim fillcolor As Long Dim colorcount As Long fillcolor = Samplecell.Interior.Color For Each bAreacell In bArea If bAreacell.Interior.Color = fillcolor Then colorcount = colorcount + 1 End If Next bAreacell CountColorIf = colorcount End Function

[@javokhirnabiev8373]

Thank you for your video I don’t have this function in my excel. What can I do?

[@BarbHendersonconsulting]

@@javokhirnabiev8373 if you watch this video, I show you the code to install the function. The code is located in the description of the video

[@jacobarbl9584]

Thank you Barb, you really helped me out, the formular works perfectly :D To people who don't know how to enter VBA ( Microsoft visual basic application) To enter VBA Press ALT + f11 Remeber to create a module and add formular there.

[@BarbHendersonconsulting]

thanks

[@fakecabal]

How do you get the countcolour? Is there a Sumifcolor?

[@xVeRSeii]

See the end of the video, there's the code in the description

[@thecalmguy_vin]

Do you have any additional input vba code for getting updated counts, as you mentioned in the end it will not count new updates. what can we do in this case?

[@RHH1095]

Very nice. Thank you. I cut-n-pasted your code and got on with my day. And you are the only one on this topic that pointed out the details about conditional formatting color (fill).

[@BarbHendersonconsulting]

Excellent!

[@shivarajhugar8915]

How to get Countcolorif function it not show in formula bar

[@BarbHendersonconsulting]

if you watch the entire video I show you how to install the code to get it

[@barakliron]

Thanks for the video. How can I sum the value of the green cells, for example?

[@BarbHendersonconsulting]

you would just add the colours =countcolorif(b6,D6:l14)+countcolorif(b7,D6:l14)+countcolorif(b8,D6:l14)

[@yasser-qv7nq]

How can i do the same in office dynamic sheet. Can not create new formula

[@BarbHendersonconsulting]

no you must install the function for this to work

[@yolyaraullo9900]

Hi Ms. Barb. I just want to let you know how grateful I am for this video. It is very helpful and I was able to use your formula/code. Thank you very much!

[@BarbHendersonconsulting]

Wonderful!

[@Hectrucci2]

that solution worked perfect to me! many thanks for this video

[@BarbHendersonconsulting]

Great to hear!

[@rodrigobasoria3191]

Thank you very much. You have helped me a lot. Keep up the great job !

[@BarbHendersonconsulting]

Glad to hear that!

[@mehrdad123]

Thank you. anyway to count conditional formatting highlighted as well too?

[@BarbHendersonconsulting]

Unfortunately not

[@kevinliffey7362]

Thank-you - I've waited years for this one!

[@BarbHendersonconsulting]

You're very welcome!

[@maxinehamilton9288]

Barb - thanks for the video - What version of Excel are you using? I have Office 365 Excel but I do not have the CountColorIf option it goes straight to CountIf - thanks! This is soooo irritating as I need this option. I couldn't even force it in manually.

[@BarbHendersonconsulting]

I have created a custom function. The code is in the description of the video.

[@kurtmolls7513]

Shift+Control+Enter after entering the formula and it's dynamic, the counts will automatically update if colors change.

[@BarbHendersonconsulting]

good to know!

[@maika2449]

You changed my life with this formula. I can't even explain it in words. Thank you worlds over for sharing this!

[@BarbHendersonconsulting]

Great to hear!

[@andiliaugustinus8409]

Hi, Barb. thank you so much for the video. how did you come up with a CountColorIf

[@BarbHendersonconsulting]

I found something similar online and modified it to fit my needs

[@AhadAliKhan18287]

Amazing really u helped me getting that done, and my manager praised me like she could not beleive i did it so quick . Thankyou

[@BarbHendersonconsulting]

Great job!

[@فهدالحربي-ت7ه]

Is there a way to update the function?

[@BarbHendersonconsulting]

no, the function must be run ever time

[@HIKAJO]

How can I use this using countifs so that it only count the colors with criteria from other columns?

[@BarbHendersonconsulting]

This function is basically doing the countif already

[@HIKAJO]

@@BarbHendersonconsulting "countifs" so the cell color and other criteria from other columns

[@nicolesamson380]

hey barb, thank you so much for the video very helpful, however im working on a piece where in a single row there will be multiple colors, i need to assign a particular value, if there are out of 5 cells, 3 are red colored cells then its "A", if 2 are green then its "B" so on and so forth, how do it create this condition?

[@BarbHendersonconsulting]

you could use an if statement. if the results =3 then "A"

[@sankarchandrabose3829]

Thanks for this video, it helped me

[@BarbHendersonconsulting]

Glad to hear it!

[@namandeepsingh6156]

Thank you Ma'am, was indeed very helpful.

[@BarbHendersonconsulting]

Glad to hear that

[@khushmindersingh962]

Very very helpful

[@BarbHendersonconsulting]

Glad to hear that

[@AhadAliKhan18287]

Just one question, if i want to do it further on some other data this code is not working. Like i added april sheet and it was not counting the color in tht sheet but thw workbook is same

[@BarbHendersonconsulting]

if you installed the code in that particular worksheet and not in the general it will only work on the one sheet

[@abuanasmosaddy1000]

my teacher The countcolorif function does not exist in Excel, how can I add it? thanks

[@BarbHendersonconsulting]

If you watch the whole video, it shows you how to install. Alt F11 and the code is located in the description of the video

[@eminipek]

Hi Barb, tanks for sharing, it was helpful. I just wonder, how can we get the VBA code interactive, I mean the count could change instantly when a new cell gets colored that should be counted. At the present condition, when a new cell gets colored, we have to go to the command cell and press enter to see the new value.

[@BarbHendersonconsulting]

Good question!, I would suggest to run it when the sheet changes but when the colour of the cell is changed it is not consider a sheet change. I am not sure how you would get this done.

[@eminipek]

@@BarbHendersonconsulting thanks for the answer.

[@smile-lv5mv]

Can i ask why i cannot see the countcolorif formula

[@BarbHendersonconsulting]

If you watch the whole video, I show you that you must install the function. The code for the function in located in the description of the video

[@steveweber702]

Thank You this is a great feature you developed. Can it be made to dynamically increment as I add colors to other cells?

[@BarbHendersonconsulting]

unfortunately, you have to run the formula again. You might try doing a general refresh on the data?

[@steveweber702]

Thank You very much for the solution

[@bernardbusonick343]

Ma'am, I dont have the countcolorif, did you create that?

[@BarbHendersonconsulting]

The code for the function is in the description of the video

[@bluejay12293]

no countcolorif on my excel how did you get that?

[@BarbHendersonconsulting]

If you watch the whole video, I show you how to install the function on your system. The code for the function is in the description of the video

[@sravan086]

can we count the colors based on conditional formatting ?

[@BarbHendersonconsulting]

I have another video for that kzbin.info/www/bejne/bIvNpGWEpr-HgLM

[@ebsulay775]

hi. is there a way to count coloured cells that is conditional formatted?

[@BarbHendersonconsulting]

I have a video kzbin.info/www/bejne/hJfPq4mno8mrjZY but it only counts cells that are in vertical columns

[@al-rajivbantuasbasir9801]

My =countcolorif does not work. Where can i download or place that code in description? Thanks for replies

[@BarbHendersonconsulting]

If you are on a windows, press F11 and that will bring you to the spot to install the code

[@adeel6868]

i am unable to put this formula in my excel sheet should i add some coding in visual basic, if there is some coding please share

[@BarbHendersonconsulting]

the code is in the description of the video

[@matthewthewizkid]

I seem to have an issue with the coding being forgotten after Excel has been shut down and reopened? Is there any fix for this?

[@BarbHendersonconsulting]

did you save it with the .xlsm extension?

[@matthewthewizkid]

@@BarbHendersonconsulting I wrote it into a new ‘module’ in the coding and clicked save? What extension?

[@BarbHendersonconsulting]

@@matthewthewizkid Xlsm

[@isxp]

How can I get this function to count the conditionally formatted cells? My count right now is 54, as I have 54 cells, but I think CountColorIf is summing them as all white cells, rather than counting the formatted cells. To make it hopefully more clear, I am trying to count the conditionally formatted green cells in a column of data. I have a conditionally formatted green cell selected in the function input as well, I believe I have set the input properly for the function. Thank you for your time and any insight you can provide.

[@BarbHendersonconsulting]

I have a video on counting conditional formatted cells kzbin.info/www/bejne/hJfPq4mno8mrjZY

[@isxp]

@@BarbHendersonconsulting That's great! Thank you for this content and your timely reply.

[@fatimamanseguiao1779]

@@BarbHendersonconsulting ty!

[@EyeIn_The_Sky]

is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True"?

[@BarbHendersonconsulting]

I answer your previous comment

[@somyaluthra]

I can't find this function in my excel

[@BarbHendersonconsulting]

you must create a custom function the code is supplied in the description of the video

[@Silent67699]

Great work

[@BarbHendersonconsulting]

Thank you so much 😀

[@zaroonbilal4986]

Thanks for making this video. Can you we do same thing on online version?

[@BarbHendersonconsulting]

VBA does not work on Excel online

[@t4tahmed427]

I cant find it in in french version.

[@BarbHendersonconsulting]

ceci n'est disponible dans aucune langue, il doit etre installe seleon la video

[@rayvanrensburg93]

Hi, I dont have the formula function "countcolorif" it gives me name error if I insert it after the =

[@BarbHendersonconsulting]

The video shows you that you must create a function. To install the function press F11 and copy the code from the description

[@sadik5397]

@@BarbHendersonconsulting Alt+F11

[@Moreofthatminecraft]

CountColorIf does not exist in my excel = March 2022. Is this outdated?

[@BarbHendersonconsulting]

If you watch the entire video, I show you how to install it on your excel

[@ieuanjones9403]

I can't get count color to come up on my excel. What do I do?

[@BarbHendersonconsulting]

Did you install the function? the code is in the description. ALT F11

[@johnx2686]

Ambiguous name detected: CountColorIf. What is this? I cannot count with this code. Any help?

[@BarbHendersonconsulting]

Ambiguous character, must be an error in your copied code

[@johnx2686]

@@BarbHendersonconsulting thanks😎

[@cynthia168]

Thanks, Barb, this is fantastic! Can you show me how to modify the code so it can count all cells with the same background color, including the ones with the numbers in them?

[@BarbHendersonconsulting]

it should work, with numbers or not

[@nashikwalaassociates]

@@BarbHendersonconsulting No it does not work with numbers in it

[@BarbHendersonconsulting]

@@nashikwalaassociates retry, I just tested it again and it works perfectly

[@rob2estonia]

Thank you! This is exactly what I needed....and it worked!

[@BarbHendersonconsulting]

Glad it helped!

[@hiteshbhilai2010]

Thanks for the clear explanation :) I was able to apply it for getting the count of colored cell in excel

[@BarbHendersonconsulting]

Glad it helped!

[@hiteshbhilai2010]

@@BarbHendersonconsulting do you have any videos on date related problems on - I need to convert from July 6th, 2021 to 07-06-2021 . Thanks for help

[@kimher7199]

This is great thank you! Although I have a problem using this: if you don't replace a colored cell by using another color over it, but instead use the "fill cell with color" button under home->font, it only updates when I add cells and not subtract! The only way to fix this is by manually telling the cell to recalculate, or else it will not do it. Suggestions for getting it to do it automatically?

[@lukasb.7122]

Does this work in the web version of excell?

[@BarbHendersonconsulting]

Sorry, vba does not work on the web version

[@hamdyhafiz4078]

Unfortunately, I cannot find any function named "councolorif" in excel 2016!!

[@BarbHendersonconsulting]

if you watch the entire video, I show you how to install the code that is included in the description of the video

[@davecks3042]

Thanks . Your videos are simple and user friendly. I am also looking for vba solution for a combo box where the drop down list depends on the option button I select. I hope you can help me on that .

[@mikelennon1078]

How can count the number of same colored cells in a row? Let's say conditional formatting turns some cell's background to Green in a row. How can we automatically get the SUM of the those Green colored cells in a row?

[@BarbHendersonconsulting]

This code will happily count colours in a row but not when they are conditionally formatted

[@bjm5813]

Use F8 to update instead of run formula twise.

[@BarbHendersonconsulting]

good idea!

[@recepbas9842]

thank you. it is very usefull.

[@BarbHendersonconsulting]

Glad it was helpful!

[@bilalahmadkhan9496]

Thank you, it helped a lot:)

[@BarbHendersonconsulting]

I'm glad I could help

[@Ty33Budd]

What if you don't have CountColorIf function?

[@BarbHendersonconsulting]

if you watch the entire video, it will show you how to install it and the code is located in the description of the video.

[@vinnydizzy]

I was looking forward to this. And in a way, it does what you say: this is just how you're able to do this. But there is one thing above all that makes me feel that this method is of little useless. And that is that if you change the content of your range, the result is no longer correct. On the other hand, I also ask myself why you keep typing your formula: can't you fix your range and just extend your formula over the following cells, while the reference cell follows?

[@kishorekarthik5030]

Madam, Please give me the solutions to the following How to count conditional formatted cell color... How to copy conditional formatted cells as it is with colour... How to give good bad and avg to the cell based on color

[@BarbHendersonconsulting]

_ have a video on counting conditional formatted cells. It however only work on vertical lists, not horizontal_

[@johnpromise2215]

thanks so much for your videos, please how can i extract a carting amount how of email from a bulk email list? for a example i have 1000000 email but i only want to extract 3000 email from the 1000000 email...please is there a formula for that

[@BarbHendersonconsulting]

email me at [e-mail] and we can discuss

[@HEMISHVAIDYA]

COUNTCOLOURIF COMMAND IS NOT PRESENT IN THE EXCEL. HOW TO ADD THE COMMAND

[@BarbHendersonconsulting]

Watch the entire video you must install the function

[@adamalday01]

you rock, thank you!!

[@BarbHendersonconsulting]

I am glad you like it!

[@prees2509]

theer is no function of countcolour in my excel.. and it islatest version of microsoft

[@BarbHendersonconsulting]

watch the video I show you how to install the function

[@phillipedward8106]

Is there anyway to do this with conditionally formatted cells? I'm trying to make my document count the number of cells that turn a certain color when information is inputted into the conditionally formatted cells...

[@BarbHendersonconsulting]

check out this video kzbin.info/www/bejne/bIvNpGWEpr-HgLM

[@alikh4161]

that is superior !! . thank you

[@BarbHendersonconsulting]

your welcome

[@luckysharma5827]

marvelous❤️

[@BarbHendersonconsulting]

thanks

[@rs-tarxvfz]

Ok I got It But I had to save the file as excel enabled sheet.

[@BarbHendersonconsulting]

if you install a function you must save it as such

[@voiceoffnb7255]

Beautiful. thanks.

[@BarbHendersonconsulting]

Thank you too!

[@haythamel-ansary9707]

thaaaaanx, it was amazing function

[@BarbHendersonconsulting]

Welcome

[@shounakkhodke4334]

Thank you

[@BarbHendersonconsulting]

You're welcome

[@shwetabanerjee4600]

Thank You.

[@BarbHendersonconsulting]

You're welcome

[@thiagotvsg]

amazing thanks

[@BarbHendersonconsulting]

Most welcome

[@np66uh]

good video tnks

[@BarbHendersonconsulting]

Thank you too

[@rs-tarxvfz]

There is no function CountColorIf

[@BarbHendersonconsulting]

you find out a lot when you watch the whole video!

[@manojkumarav9093]

COUNTCOLORIF FUNCTION NOT SHOWING IN FUNCTION MENU

[@BarbHendersonconsulting]

If you follow along my video it will show you that you need to create and install the code for the function. ALT f11 to access the spot and the code is located in the description of the video.

[@fatimamanseguiao1779]

@@BarbHendersonconsulting Sorry😔 i would like to know how to create the CountColorIf function and install it mam barb?

[@BarbHendersonconsulting]

@@fatimamanseguiao1779 You must install the code provided in the description in the vba Window. If you are working on a PC press Alt F11 to access this window

[@enzorific]

cant hear you!!!

[@BarbHendersonconsulting]

read the sub titles then

[@legockysandor]

This is much easier in Numbers in iWork

[@BarbHendersonconsulting]

good to know

[@legockysandor]

@@BarbHendersonconsulting Do you use sometimes Numbers as well?

[@neodasa2]

Thank you, but you took too long to say what you wanted to say.

[@BarbHendersonconsulting]

I try

[@aloysiuslan3813]

wow really not for beginner.

[@BarbHendersonconsulting]

true