I'm offering personal coaching to only 2 people. Get details now ml.up4excel.com/htcapp04 - Offer Ends 19th August 2022
@stephenrostron43933 жыл бұрын
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
@Up4Excel3 жыл бұрын
Nice work! Anything that saves people time is good in my book 👍
@Up4Excel5 жыл бұрын
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.
@Up4Excel3 жыл бұрын
👉 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!
@p1943LEB2 жыл бұрын
By far, the clearest, easiest-to-follow explanation I've seen. Thanks to you, I'm going to look like a GENIUS!
@Up4Excel2 жыл бұрын
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 😉
@rbc8972 жыл бұрын
I have watched several tutorial in KZbin about this formula but I still dun understand except U!! Thank you for ur teaching!! ❤
@Up4Excel2 жыл бұрын
Happy to hear that...glad to help 👍
@bigsmoke2803 ай бұрын
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?
@Up4Excel3 ай бұрын
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
@livewire89492 жыл бұрын
You made this very easy compared to other vidoes I watched. Thank you
@Up4Excel2 жыл бұрын
Thank you for your feedback on this. Happy you found it easy to follow 👍
@muhammadbelmokhtar10914 жыл бұрын
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!
@Up4Excel4 жыл бұрын
Really glad to hear that! Thanks for the sub 👍
@wayneedmondson10654 жыл бұрын
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!
@Up4Excel4 жыл бұрын
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 😀
@johnborg54194 жыл бұрын
Amazing John. Never knew the +Now()*0.....that was Great!!! : ) : )
@Up4Excel4 жыл бұрын
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.
@johnborg54194 жыл бұрын
@@Up4Excel Thanks for sharing John : )
@getiingtubed3 жыл бұрын
This is very useful
@Up4Excel3 жыл бұрын
Glad you think so 👍
@blackgerbera3 жыл бұрын
Great, Thanks!
@Up4Excel3 жыл бұрын
Glad you liked it 👍
@swednecktrailers41466 ай бұрын
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?
@Up4Excel6 ай бұрын
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
@rajuj88766 ай бұрын
Sir I used conditional formatting for filling I try work on it but not working What I have to do
@Up4Excel6 ай бұрын
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.
@manojkumars24274 жыл бұрын
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.
@Up4Excel4 жыл бұрын
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 👍
@mikelennon10783 жыл бұрын
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.
@Up4Excel3 жыл бұрын
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 👍
@abbadubenzwin51572 жыл бұрын
But if many cells of columns are attached and contain numbers or colored format , then another table must be written to count and compare..?!
@Up4Excel2 жыл бұрын
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.
@SMARTOFFICERSL11 ай бұрын
What is 63 means?
@Up4Excel11 ай бұрын
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
@SMARTOFFICERSL11 ай бұрын
@@Up4Excel is this working for conditional formatting. I could not found correct answer.
@Up4Excel11 ай бұрын
@@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.
@mikelennon10783 жыл бұрын
Does anyone knows how to count and show the total number of conditionally formatted background color ( let's say those which turned green ) ?
@Up4Excel3 жыл бұрын
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 🤞
@mumtazharoon88924 жыл бұрын
how to sum all couloms but not sum in the range who colored coloms
@Up4Excel4 жыл бұрын
just do what I did in the video and take the result away from the total sum.
@mohammadnabil5144 жыл бұрын
How can it be used for row?
@Up4Excel4 жыл бұрын
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.Sandman2 жыл бұрын
@@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.Sandman2 жыл бұрын
than you in advance
@Up4Excel2 жыл бұрын
@@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?
@faustorossi55249 ай бұрын
What language do you speak?
@Up4Excel9 ай бұрын
English, I'm from England.
@faustorossi55249 ай бұрын
@@Up4Excel Very well sir, I'm from London but I barely understand you.
@Up4Excel9 ай бұрын
@@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.