Excel COUNTIF & SUMIF On Colour - No VBA Required

[@johnklein2253]

Thank you so much for this. I had been searching endlessly for a solution without VBA and this works perfectly .

[@consistoire-de-paris]

It's not great, it's brilliant !!! thanks et congrats for this elegant and original solution !

[@fliptoptan]

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?

[@albertcastorena8836]

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.

[@bernadettevines8948]

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

[@NeoMennon]

Thank you, this totally solved my problem, even though I still not quite understand how the function actually works.

[@ChesterTugwell]

Glad it helped.

[@BachiroH24]

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

[@DNut10]

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 ?

[@ranatrbudhavongnant1564]

Thank you for your tutorial! it's literally useful.

[@yourbutlerza]

Thank you!!! Excellent tutorial. I will definitely be back for more from Chester :)

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

[@JinaneKarhani]

Thank you, very easy with no macro, but why did you choose 38 ?

[@nonoobott8602]

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.

[@dempseyroll96]

The fact that it doesn't apply to conditional formatting is really a deal breaker.

[@andrewwest5344]

YEAH it sure is! I just spent ages thinking it was something I did wrong!

[@aliasger.kalolwala]

@@andrewwest5344 Same dude

[@cammer46]

I just spent an hour trying to get this to work. If only I had read your comment first!!!

[@malcolmlawrence5148]

why don't the values in the total sum cell change on real time if input amount was changed

[@argusphobia3694]

Thanks brother

[@TheLooking4sunset]

So useful

[@noeliabs26]

you are a genius !!! thank youuuuuuuu

[@natarajanvignesh5641]

Amazing sir...

[@wendyfinn5165]

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?

[@johnmathews6769]

That I38 was confusing, rest was great

[@shesnsjsb7470]

THIS IS NOT WORKING FOR ME IM GETTING #NAME? INSTEAD OF A NUMBER

[@ambarilhamchannel]

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_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"?

[@davidjordan3703]

Very helpful. Thank you

[@ionelagherman5952]

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

[@thinhtang11]

does it work the same with row data?

[@liptaklorinc3861]

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!

[@kpura947]

I'm wondering this same thing

[@laussanemaryjoan8417]

Help please. What is the index numbers to extract other text - you mentioned in your tutorial.

[@SmaragdinaTibia]

thank you thank you thank you

[@abasiyanikmf]

thanks

[@kristopherkenyon6090]

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?

[@gagahguk]

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

[@usmanmirza2438]

what is 38 & why result is 6

[@tubizer]

Your answer is here, excel color index

[@RMLearningHub]

It's to very helpful videos

[@vladyslavstadnyk2762]

why I'm getting #name error?

[@guidlines]

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.

[@chubak8]

Beautiful. Thanks bro

[@faizalrasep1436]

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

[@mdarifhussain1]

It is better I count the colors myself then use such high level rocket science stuff!!!!!!

[@hassanisa1742]

I can't get cell colour .😓😓

[@thomasavinash72]

this number 38 is code for yellow what of other colour's ? can't find on the net

[@shirleytan7776]

It's not working for my MS office 360. The name manager formula is not recognisable by the system.

[@kvmprasad]

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 ?

[@vicsharma]

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.

Try bypassing this and going straight to VBA.

[@elDeMISe]

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?

[@jauri8994]

Does "get.cell" still exist? I tried it with "cell" but it doesnt work..

[@neliana7115]

Thanks sir 👍

[@khamishoufar1019]

Thanks, is there a way to count by font colour?

[@traysharpe4610]

Thank you! I have been looking for the other index numbers to extract other text - mentioned in your tutorial. Can you help please?

[@oguzhancetin_mech]

hi is there a work around to automatically update the color numbers when the color is changed?

[@prashithkannoth7520]

What is 38 ?

[@tomasmartinaitis9475]

hello for me not working, getting error that it is a formula and excel want to count something

[@rocketedtech]

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?

[@pilarfdm]

Thanks, super useful

[@enriqueramos3506]

how did you get the value of argument 38?

[@sammywel12]

Great

[@kevinskogg2179]

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.

[@barbarapatterson964]

It didn't work for me. I get #NAME? error

[@aravindktm5957]

same I have tried but its not calculating

[@Nonamebutaface]

My excel doesn't have the Name Manager function at all, this can't be done without it I'm assuming "sigh"

[@ezequiasrocha3037]

Do you have the formula for the font colour?

[@ipeventservices3094]

im trying to use the get.cell function however i am getting an error of #NAME can you please help

[@iecivil]

Even I'm getting the same error. Any solution on this?

[@xxxxafghxxxx]

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.

[@EricCawaling]

How did you get the 38?

[@ashleyboykin7777]

what does 38 represent?

[@EurysSantos]

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?

[@yoda7105]

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

[@EurysSantos]

@@yoda7105 Super!!! Thanks

[@hrideyesh9139]

@@yoda7105 So 38 will always work to get color?

[@yoda7105]

@@hrideyesh9139 unless Microsoft changes it

[@jaymeez]

Can this be done in numbers?

[@RafaelJuck]

I don't know if that's a dumb question, but what if I want to count using the font color not the background?

[@ChesterTugwell]

Try kzbin.info/www/bejne/jnfTgJKnZq19jJo

[@rohrl77]

Super interesting Method. Didn't know about this! Thanks for the explanation Chester! Do you have a website by any chance?

[@ChesterTugwell]

bluepecantraining.com

[@sedaclayton-greene1747]

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

[@hemanthasisirakumara305]

Hi how to vlookup cell color value in excel

[@elsaddefo]

Is the number 18?

[@milo1351]

Where did 38 come from!?

[@FRANKWHITE1996]

Nice :)

[@amarnathamal3560]

What is 38 you will put on default can you plz explain, concept cant understand that point.

[@N8riz]

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.

[@kamiolarte5127]

may be you can explain what the 38 is??

[@ChesterTugwell]

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.

[@sandeepshrestha8116]

Why we can use only 38 in that formula why not other number, please explain me ??

[@pawanbhardwaj848]

The 38 is the index number for the cell's colour

[@Ricas3118]

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

[@Cypresalle39]

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

[@aerojean11]

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

[@chriscabel9473]

why 38?

[@kirandeepchoudhry9232]

From where you get this get color I can't find it Chester.Plz make it clear

[@richfan10]

Just google ‘excel color index’

[@marceloribeirosimoes8959]

CellColour ???????????????? Where did this came from?

[@tomas12912]

What the hell with this damned excel! If I want to write 38 there, the excel report me an error and I cant continue....

[@guy1281]

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