Uncover Hidden Excel Powers: Sum & Count Cells by Color!

[@Up4Excel]

I'm offering personal coaching to only 2 people. Get details now ml.up4excel.com/htcapp04 - Offer Ends 19th August 2022

[@stephenrostron4393]

Just used your technique on over 1000 lines of data with multiple columns and several classifications (colours) - worked a treat and saved me hours - thanks

[@Up4Excel]

Nice work! Anything that saves people time is good in my book 👍

[@Up4Excel]

Sum or Count by Cell Colour using the hidden Excel function GET.CELL option 63. Plus....unlock a whole new world of long forgotten Excel Functions using the 2 methods taught in this video. Once you know how to access this function you can SUM, COUNT, AVERAGE, MAX, MIN etc by almost any cell format including Font Size, Font Colour, Font Name and even Row Height.

[@priyachandrasena7729]

Thank you very much for your generosity ! Your step-by-step video helped me a lot.

[@Up4Excel]

👉 NEW: Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!

[@p1943LEB]

By far, the clearest, easiest-to-follow explanation I've seen. Thanks to you, I'm going to look like a GENIUS!

[@Up4Excel]

Thanks for the complement, glad you think so. Don't look like too much of a genius or you'll get all the work thrown at you 😉

[@rbc897]

I have watched several tutorial in KZbin about this formula but I still dun understand except U!! Thank you for ur teaching!! ❤

[@Up4Excel]

Happy to hear that...glad to help 👍

[@bigsmoke280]

Hi I found your videos today and have just been going thru them for the past few hours. Thank you I am learning a lot. I was just wondering is there a solution around conditional formatting?

[@Up4Excel]

Glad you're learning a lot and happy you find me. There isn't a solution regarding conditional formatting that I know of and I suspect that if there is one it'll be something completely different to this technique. Conditional formatting sits over the top of other formats applied to cells and is temporary. So even if, for example, you format a cell yellow, conditional formatting can change it to red, and the format of the cell will still show up as yellow in almost any kind of integration I know of, including the one in this video. So sorry, no solution I know of. John

[@livewire8949]

You made this very easy compared to other vidoes I watched. Thank you

[@Up4Excel]

Thank you for your feedback on this. Happy you found it easy to follow 👍

[@muhammadbelmokhtar1091]

Hey man this really is an awesome video, I needed to learn this so that I could apply it to my inventory sheet and get the total retail value of my inventory without having to manually calculate everything everytime and this really did the trick! You got a thumbs up from me and a subscribe! Thanks again!

[@Up4Excel]

Really glad to hear that! Thanks for the sub 👍

[@wayneedmondson1065]

Hi.. interesting.. usually I default to a VBA user defined function to solve something that is not native to the EXCEL workbook/worksheet. I'll have to give these Excel 4 Macro Functions a try. I guess the only downside is if or when Microsoft eliminates them from the base EXCEL code in the future. Thanks for sharing. Thumbs up!

[@Up4Excel]

It's amazing Microsoft still keeps these features in the base code really. Perhaps they want to maintain full backward compatibility....or perhaps the programmer has long since left them and they daren't remove the code and risk unknown knock-on impacts 😀

[@johnborg5419]

Amazing John. Never knew the +Now()*0.....that was Great!!! : ) : )

[@Up4Excel]

Yes, it's a great trick....I found it online originally, but can't remember where. I'm sure there are many other uses I'm yet to discover.

[@johnborg5419]

@@Up4Excel Thanks for sharing John : )

[@getiingtubed]

This is very useful

[@Up4Excel]

Glad you think so 👍

[@blackgerbera]

Great, Thanks!

[@Up4Excel]

Glad you liked it 👍

[@swednecktrailers4146]

This is great, i am able to replicate it. However, i am hoping to have the range of multiple columns and multiple rows. is that possible? only pick up and sum red cells from c4:e27 for example?

[@Up4Excel]

Not directly as you can only do one cell per formula, but you could have multiple columns of these formulas picking up one column each and then a sum all of them at the end. i.e. The formula in column F picks up column C, G picks up D, H picks up E...then sum results from F to H. Note you would need to change the GET.CELL function to take a cell 3 places to the left, rather than one place to the left as I did in the video. Hope that makes sense. Good luck 👍John

[@rajuj8876]

Sir I used conditional formatting for filling I try work on it but not working What I have to do

[@Up4Excel]

This method can't detect conditional formatting, only set cell formats. I'm not sure if there is anything that can detect conditional formats. Sorry. John

[@29sthomas]

Hi I tried using the sheet in the description, After making the update using the Name manager when i selected the name field , the cell gave an error #Name? could you help me with this ?

[@Up4Excel]

Make sure you have macros enabled as this might cause issues if not. Other than that, try downloading the sample file I provide with the video (link is in the description) and comparing it as maybe there is something different in your version.

[@pjaj43]

Great video. Pity it won't work on a G-sheet

[@Up4Excel]

As with quite a lot of other Excel features too unfortunately.

[@manojkumars2427]

Hi.. I did not understand the way macro worked.. I did not see you saving the macro.. Have a disconnect.. Please help me with the Macro section and how you used it.

[@Up4Excel]

Hi, it's not a macro in the modern sense. It is a command from an old macro language, but Excel still recognises the command if you use it as part of the definition of a named range. This is where the magic happens 👍

[@mikelennon1078]

Lets say I have 1000 records but only certain row id's are colored. I would like to have a column which find the next colored ID and give it an incremental number. i.e. the first colored id is in row 10, the second row 12, the third in row 40. And I would like to get a 1,2,3,...... in front of row 10, row 12, row 40, ................... respectively. Would you please let me know how this can be done? I would appreciate it.

[@Up4Excel]

My main tip is don't use color for this sort of work as Excel really isn't setup to pick up color. If you need to use color then my second suggestion is to have a column with real data (i.e. not color) and use conditional formatting on the ID column based off what is in the real data column. You can always hide the real data column. If you do this you can use standard formulas etc to do what you want. To use the system in the video you could use helper columns with the color numbers and base things off that. There's way to much to explain here though so you will have to investigate based on the tips I've suggested. Hope you solve it 👍

[@abbadubenzwin5157]

But if many cells of columns are attached and contain numbers or colored format , then another table must be written to count and compare..?!

[@Up4Excel]

Yes, the system has limitations but not sure of a way around that. Even a custom VBA function would require a separate table in your example.

[@SMARTOFFICERSL]

What is 63 means?

[@Up4Excel]

There should be a list of all the options on the video description and what they mean. 63 is the code for fill colour of the cell

[@SMARTOFFICERSL]

@@Up4Excel is this working for conditional formatting. I could not found correct answer.

[@Up4Excel]

@@SMARTOFFICERSL No, it only works for actual formatting in the cells. Conditional formats sit on top of those formats so can actually cover up a format that this formulas is detecting...may be useful or maybe not depending on what you want.

[@mikelennon1078]

Does anyone knows how to count and show the total number of conditionally formatted background color ( let's say those which turned green ) ?

[@Up4Excel]

You're definitely into VBA here Mike. The only way I've found is to effectively construct code that detects the same thing the conditional format does, and then count that. Hopefully someone else has more knowledge and can suggest a better way... as I'd love to hear it 🤞

[@mumtazharoon8892]

how to sum all couloms but not sum in the range who colored coloms

[@Up4Excel]

just do what I did in the video and take the result away from the total sum.

[@mohammadnabil514]

How can it be used for row?

[@Up4Excel]

When you set up the named range, instead of clicking the cell to the left, click the cell above. You can then use the formula to detect the colour above rather than to the left. Use the same sumif or countif technique on the results. How this makes sense.

[@FL.Sandman]

@@Up4Excel Thank you for these tips you make it easy to understand. referring to the question above in the name range can you input all four cells surrounding the target for above below and to sides I am unable to setup in a row as you had since our colors indicate a certain function complete at a particular location on a specific target

[@FL.Sandman]

than you in advance

[@Up4Excel]

@@FL.Sandman I appreciate it's a late reply but hopefully still helpful. You could have a formula refer to all the cells around by having a long nested IF statement...but it is very messy and unlikely to be your best solution. Usually when I see this kind of question it is because the road you have gone down has led to more complex requests than an alternative you perhaps don't know or haven't spotted. I suggest going back a few steps in your entire spreadsheet workflow to see if taking a different approach might make things simpler. Best of luck.

[@patriciacastro9993]

@@Up4Excel Thanks very much, I applied the formula in both columns & rows and worked perfectly. However, I saved & closed the file and opening it again, and try to edit info, the formulas disappeared and the further calculations I made with those results changed. Is there a way to keep them or how to call them back into the file?

[@faustorossi5524]

What language do you speak?

[@Up4Excel]

English, I'm from England.

[@faustorossi5524]

@@Up4Excel Very well sir, I'm from London but I barely understand you.

[@Up4Excel]

@@faustorossi5524 Most people seem to think I'm from London, but actually from south west England.

[@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 Please advice, thx in advance.

[@brensonyeo1358]

It turns out that I had to swap "," with ";" instead.

[@Up4Excel]

Glad you got it solved 👍