Thank you so much for this. I had been searching endlessly for a solution without VBA and this works perfectly .
@consistoire-de-paris2 жыл бұрын
It's not great, it's brilliant !!! thanks et congrats for this elegant and original solution !
@fliptoptan3 жыл бұрын
This is FANTASTIC! I've been wanting to do this for years! I know you use #38 here, and having watched other videos of yours, you say that other numbers do other things. Where can I find a list of these things please?
@albertcastorena88364 жыл бұрын
Agreed about the cell color... You could've showed us what color fill you chose so we could have followed along from end to end.
@bernadettevines89484 жыл бұрын
Thank you soooooo much Chester! I've been working with a list for some time and was asked for a tally. It just happened to have three different items that I color coded but still had to count. I knew there had to be an easier way. I LOVE EXCEL!!!!
@NeoMennon4 жыл бұрын
Thank you, this totally solved my problem, even though I still not quite understand how the function actually works.
@ChesterTugwell4 жыл бұрын
Glad it helped.
@BachiroH244 жыл бұрын
So informative vid but I've two questions: 1-why 38? 2-why this doesn't work with conditional formatting? Thanks for help me guys! 😊
@DNut103 жыл бұрын
yeah, kinda useless if it doesn't apply to conditional formatting (implies you're doing everything manually anyway)
@cassyop Жыл бұрын
@@DNut10 what is conditional formatting? Is this something that I can select?
@preranavishe2408Ай бұрын
same question why 38 ?
@ranatrbudhavongnant156411 ай бұрын
Thank you for your tutorial! it's literally useful.
@yourbutlerza2 жыл бұрын
Thank you!!! Excellent tutorial. I will definitely be back for more from Chester :)
@mikelennon10783 жыл бұрын
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 each row. How can we automatically get the SUM of the those Green colored cells in each row? My purpose is to Find all records which have 4 or more green cells. Hope it makes sense.
@JinaneKarhani5 жыл бұрын
Thank you, very easy with no macro, but why did you choose 38 ?
@nonoobott86023 жыл бұрын
38 is the type_num that returns the cell index. For more on this, you can check out "Excel 4 Macro" functions and learn about all of them and how they work.
@dempseyroll964 жыл бұрын
The fact that it doesn't apply to conditional formatting is really a deal breaker.
@andrewwest53444 жыл бұрын
YEAH it sure is! I just spent ages thinking it was something I did wrong!
@aliasger.kalolwala3 жыл бұрын
@@andrewwest5344 Same dude
@cammer462 жыл бұрын
I just spent an hour trying to get this to work. If only I had read your comment first!!!
@malcolmlawrence51482 ай бұрын
why don't the values in the total sum cell change on real time if input amount was changed
@argusphobia369410 ай бұрын
Thanks brother
@TheLooking4sunset Жыл бұрын
So useful
@noeliabs263 жыл бұрын
you are a genius !!! thank youuuuuuuu
@natarajanvignesh5641 Жыл бұрын
Amazing sir...
@wendyfinn51653 жыл бұрын
I was able to get this formula working but I want to be able to change numbers in the coloured cells but I get a #NAME? Error. How do I fix this?
@johnmathews67692 жыл бұрын
That I38 was confusing, rest was great
@shesnsjsb74705 ай бұрын
THIS IS NOT WORKING FOR ME IM GETTING #NAME? INSTEAD OF A NUMBER
@ambarilhamchannel10 ай бұрын
Can we make it automatically by vba? I tried by vba function target.calculate, but it doesn't work at all. I used to have meeting, and some cells coloured with green for complete parts to assembly, yellow for partial complete, and red for not complete at all. When I was asked how many red, yellow and green, I use that formula, but if some cells color changed. That function need to be recalculate. Thank you very much for your paying attention
@EyeIn_The_Sky3 жыл бұрын
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"?
@davidjordan37034 жыл бұрын
Very helpful. Thank you
@ionelagherman59524 жыл бұрын
Hi! Is there a way to do the same on Google Sheets? Thank you!
@tigerot Жыл бұрын
I have looked everything in google and haven't found an easy way, and I would like to have one, how Microsoft hasn't thought of this? I have web excel that I would like to count the colors cell as we add and remove names and backgrounds but haven't found a way yet... this was helpful but still cant help me :(
@thinhtang113 жыл бұрын
does it work the same with row data?
@liptaklorinc38612 жыл бұрын
Hy! Awesome video, might apply it in the future, but there is one thing. It looks like it does not work in case of conditional formatting. Is it any way to do it? Should I maybe just use a different attribution number instead of 38? Thanks in advance!
@kpura9477 ай бұрын
I'm wondering this same thing
@laussanemaryjoan84172 жыл бұрын
Help please. What is the index numbers to extract other text - you mentioned in your tutorial.
@SmaragdinaTibia4 жыл бұрын
thank you thank you thank you
@abasiyanikmf3 жыл бұрын
thanks
@kristopherkenyon60905 жыл бұрын
It's not working for me... is it because the cells I'm wanting it to sum have background determined by a conditional formatting? How can I still use this if the cell has a conditional format?
@gagahguk3 жыл бұрын
already found solution dude? i'm facing same problem with you
@khaliq-fg1bbАй бұрын
not working it shows me number 0 instead how to fix this ? i did try all your step
@usmanmirza24385 жыл бұрын
what is 38 & why result is 6
@tubizer5 жыл бұрын
Your answer is here, excel color index
@RMLearningHub4 жыл бұрын
It's to very helpful videos
@vladyslavstadnyk27622 жыл бұрын
why I'm getting #name error?
@guidlines2 жыл бұрын
I did the sum CellColour formula & it worked fine. I then linked another spreadsheet to the totals. Now when I open the spreadsheet with the CellColour formulas in it & add more lines to the data, I get a #BLOCKED message coming up when refreshing the cells. Why is this? I have a registered version of Office365 & am connected online. I removed the links & still get the #BLOCKED error message when I try & update new cells.
@rachmanntennis8378 Жыл бұрын
Why does my Excel program just shut itself off the moment I type in or choose =CellColour like at 1:50? Even if I have more than 2 tabs of Excel open, it just immediately closes the programs.
@chubak85 жыл бұрын
Beautiful. Thanks bro
@faizalrasep14364 жыл бұрын
Chubak Miaouw yes its wonderfull... but i want the figure change when i change the colour, how to do that...don’t want to keep on copy and paste the formula..
@mdarifhussain13 жыл бұрын
It is better I count the colors myself then use such high level rocket science stuff!!!!!!
@hassanisa17425 жыл бұрын
I can't get cell colour .😓😓
@thomasavinash722 жыл бұрын
this number 38 is code for yellow what of other colour's ? can't find on the net
@shirleytan77763 жыл бұрын
It's not working for my MS office 360. The name manager formula is not recognisable by the system.
@kvmprasad2 жыл бұрын
I have a row with three different background colors and i need to get the count of the colors in the row. Is that doable ?
@vicsharma4 жыл бұрын
Awesome and again I agree that you can't update automatically if you change the color on a cell. Let me know if there is other way to do it.
4 жыл бұрын
Try bypassing this and going straight to VBA.
@elDeMISe2 жыл бұрын
It does if you colour with format painter, although not helpful if you are not using the same colour over and over again.
@nurhaidahnurhaidah5124 Жыл бұрын
Does it work for conditional formatting colour?
@jauri89944 жыл бұрын
Does "get.cell" still exist? I tried it with "cell" but it doesnt work..
@neliana71153 жыл бұрын
Thanks sir 👍
@khamishoufar1019 Жыл бұрын
Thanks, is there a way to count by font colour?
@traysharpe46105 жыл бұрын
Thank you! I have been looking for the other index numbers to extract other text - mentioned in your tutorial. Can you help please?
@oguzhancetin_mech7 ай бұрын
hi is there a work around to automatically update the color numbers when the color is changed?
@prashithkannoth75203 ай бұрын
What is 38 ?
@tomasmartinaitis94752 жыл бұрын
hello for me not working, getting error that it is a formula and excel want to count something
@rocketedtech7 ай бұрын
Is it possible to change the background color of a shape, based on the value in another cell? For example, let's say I have a cell with a percentage, and I want the background color of a shape to change based on percentage ranges within that cell. So, if the percentage is 30% or less, the background of the shape would turn to red. If it was between 60% and 31%, it would change to yellow. And then if the cell contained a percentage of 61% or above, it would be green. Is it possible to do this without using VBA?
@ansonleung3943 Жыл бұрын
why name manager disappeared when sending out the saved file?
@pilarfdm5 жыл бұрын
Thanks, super useful
@enriqueramos35062 жыл бұрын
how did you get the value of argument 38?
@sammywel122 жыл бұрын
Great
@kevinskogg21793 жыл бұрын
What do you know about the function Cell()? That is supposed to give a number for Cell("color",D4). I haven't figured out yet although Cell("Format",D4) does work, as well as most of the other options "Row", "Col", etc.
@barbarapatterson9644 жыл бұрын
It didn't work for me. I get #NAME? error
@aravindktm59574 жыл бұрын
same I have tried but its not calculating
@Nonamebutaface3 жыл бұрын
My excel doesn't have the Name Manager function at all, this can't be done without it I'm assuming "sigh"
@ezequiasrocha30372 жыл бұрын
Do you have the formula for the font colour?
@ipeventservices30943 жыл бұрын
im trying to use the get.cell function however i am getting an error of #NAME can you please help
@iecivil3 жыл бұрын
Even I'm getting the same error. Any solution on this?
@xxxxafghxxxx4 жыл бұрын
The only way you could have the colors updated if you save a color keys on the sheet. For example, list all of the colors you will be using, then copy the color you will need from the list every time instead of using the menu bar.
@EricCawaling2 жыл бұрын
How did you get the 38?
@ashleyboykin77773 жыл бұрын
what does 38 represent?
@EurysSantos4 жыл бұрын
Suuupeeeerrr!!! I remember that I spent a lot of time looking for a way of how doing this, at last I had to use vba and even if I couldn't get what I expected but this is awesome. One thing, I didn't understand very well why using number 38 in the function?
@yoda71054 жыл бұрын
38 is used to get the color of the cells. The function is used to get various informations of a cell and 38 is to get the color. Other numbers will get other informations. I hope this helps
@EurysSantos4 жыл бұрын
@@yoda7105 Super!!! Thanks
@hrideyesh91394 жыл бұрын
@@yoda7105 So 38 will always work to get color?
@yoda71054 жыл бұрын
@@hrideyesh9139 unless Microsoft changes it
@jaymeez2 жыл бұрын
Can this be done in numbers?
@RafaelJuck4 жыл бұрын
I don't know if that's a dumb question, but what if I want to count using the font color not the background?
@ChesterTugwell4 жыл бұрын
Try kzbin.info/www/bejne/jnfTgJKnZq19jJo
@rohrl774 жыл бұрын
Super interesting Method. Didn't know about this! Thanks for the explanation Chester! Do you have a website by any chance?
@ChesterTugwell4 жыл бұрын
bluepecantraining.com
@sedaclayton-greene17473 жыл бұрын
@@ChesterTugwell hey there, can I send a sheet to check my errors? I can get the cell colour number sorted but can't get the countif formula to work?
@hemanthasisirakumara3055 жыл бұрын
Hi how to vlookup cell color value in excel
@elsaddefo4 жыл бұрын
Is the number 18?
@milo13513 жыл бұрын
Where did 38 come from!?
@FRANKWHITE19965 жыл бұрын
Nice :)
@amarnathamal35604 жыл бұрын
What is 38 you will put on default can you plz explain, concept cant understand that point.
@N8riz4 жыл бұрын
He stated that 38 is a type of pull reference particularly, color. any other number must be a pull reference of font, icon, etc imo.
@kamiolarte51275 жыл бұрын
may be you can explain what the 38 is??
@ChesterTugwell5 жыл бұрын
With Get.Cell you can retrieve data about a cell including background colour. The 38 is the index number for the cell's colour. Other numbers retrieve other info about the cell. Microsoft documentation on this is sparce.
@sandeepshrestha81165 жыл бұрын
Why we can use only 38 in that formula why not other number, please explain me ??
@pawanbhardwaj8484 жыл бұрын
The 38 is the index number for the cell's colour
@Ricas31184 жыл бұрын
@@pawanbhardwaj848 so where i can see the number of the color i want ?
@menime3933Ай бұрын
"No VBA Required" // Follows exact instructions, Excel: "VBA required to resume"
@Cypresalle394 жыл бұрын
Question for you.. first of all thanks for the trip. Wondering if it is possible to assign a color a value? That is to sat when I highlight a cell that I can associate that color with a value always equaling 10 as an example.. best, Edward
@aerojean114 жыл бұрын
In the conditional formatting.. highlight cells....text that contains...10..... then instead of "light red fill with dark red text" click the drop down choose custom format... press okay.... choose tab fill.... choose the color...choose ok.... then okay
@chriscabel94734 жыл бұрын
why 38?
@kirandeepchoudhry92325 жыл бұрын
From where you get this get color I can't find it Chester.Plz make it clear
@richfan103 жыл бұрын
Just google ‘excel color index’
@marceloribeirosimoes89595 жыл бұрын
CellColour ???????????????? Where did this came from?
@tomas129124 жыл бұрын
What the hell with this damned excel! If I want to write 38 there, the excel report me an error and I cant continue....
@guy12814 жыл бұрын
Im having the same problem
@brensonyeo1358 Жыл бұрын
My =get.cell formula didn't work, "There is a problem with this formula" notice popped up. saved my workbook as macro-enabled .xlsm, yet no help