SUM AND COUNT CELLS BASED ON COLOR IN EXCEL (NO VBA)
ฝัง
- เผยแพร่เมื่อ 28 ส.ค. 2024
- In this video I demonstrate how to sum and count cells based on cell color. Excel has functions called "Excel 4 Macro" functions. They've existed and were there before we had Excel VBA. These functions are still compatible with recent versions of Office. There are a number of them. The one that helps us get the Color Index for a cell is the GET.CELL. The type_num that returns this color index is 63. With the SUMIFS and COUNTIFS function I can get the sum and count based on the "Color Index" criteria respectively.
EXCEL 4 MACROS
See below link to Excel's MVP Mynda Treacy's Ebook
www.myonlinetr...
ACCESORIES
Screen Recorder: screencast-o-m....
Microphone/Headset: www.amazon.com....
#MsExcel #Trending #Excel #MVP #ExcelMVP # #SumCellsByColorNoVBA #CountCellsByColorNoVBA #SumCellsByColor #CountCellsByColor #Excel4Macros #GET.CELL
thank you! Easier than VBA, and my workbook doesnt get slow by this :)
Thank you for the feedback. Absolutely appreciate!
Excellent. I've tried this based on a number of videos. Yours is the first that actually works consistently by applying the NOW function. Well done!
Thanks so much for the feedback. I absolutely appreciate and I'm encouraged
Totally agreed.
Absolutely amazing!
Thank you so much for your feedback
Good tutorial buddy, Do you know why can't it work when you set a conditional format (such as color condition) in a cell?. Thanks in advance
Awesome training!
Thanks for your feedback. Absolutely appreciate
Help! By accident i didn't save it as a Macro-Enabled Workbook the first time and now I can't go back and re-save it. I am using a Mac. Do you know how I can re-save it?
Great video & it is helpful. I want to ask how about the other ways round when we transpose the data? Can we do the sum & count on this part?
Thanks so much for your feedback. Even if you do a transpose on the data, you won't be able to sum based on color. So, I'd say this is one way you can do it easily
@@nonoobott8602 Got it, thanks. It's already good enough for me on this part. ^_^
great video.
do you have a list of all the number requests .
like 63 for the get.cell Function.
I have seen other videos that use 38.
Where did you get the code numbers.?
Do you have a list or reference where i can review the list . Thanks!
Thanks for your feedback. One of Excel's MVP "Mynda Treacy" has an article on it. Here's a link to all the "Excel 4 Macto Functions":
www.myonlinetraininghub.com/excel-4-macro-functions.
Regards
I'm trying soooo hard to get this right as it's just what I need, but I am told by excel that VB projects must be saved in macro enabled workbooks???
What are you trying to get right please?
And yes if you have Macros in your workbook, you need to need to save it as a macro-enabled workbook. That's correct!
please help. How do i count not-coloured cells that are in-between highlighted cells in a column? my column has multiple cells that are higlighted. i dont want a grand total count of all clear cells. rather i need a count of each small section of not-coloured cells that lie between highlighted cells. in other words, it must return count each time it gets to a highlighted cell
Thanks for your feedback. However, I'm not so clear. Could you be more explicit in your question. Thank you
At the first time, I follow the steps and then i can get the result. But after I save it using xlsm file. the color index figures become 0 for all color.
Issue solved. That is because i did not select the cell at the left hand side of the original cell when create the SumColor at the very beginning.
How do you get the color index?
The colour index is what is returned when you use Excel 4 Macro function. As in this tutorial, the color index is the number assigned to the fill color
@@nonoobott8602 How do I get that Excel 4 Macros function a I've followed you step by step but I can't get the colour index?
Thanks
@@beverleylowry5888 Excel 4 Macros is on your Excel version by default. However, you need to know that it only works with the name manager. Check the description for my video and you will see a link to Mynda Treacy's blogpost on them. Please let me know if this was useful
Please how do I get the colour index number?
ok
how did you get the 63?
Great question. Now Excel had what they called Excel 4 Macros and this was before VBA. These functions worked within the name manager and are still compatible with recent Excel versions.
My Mynda Tracey, a renowned Excel MVP has a detailed article on all the Excel 4 Macros functions. Each one has a type_num. To get the color index, type_num is 63. See link to article:
www.myonlinetraininghub.com/excel-4-macro-functions
Hope this helps
Hi
Help! Countifs works, sumifs does not :(
Thanks for your feedback. Pls can you be more explicit so I can help
@@nonoobott8602 I have followed all step by step countifs formula works, sumifs gives value! Tried few time step by step all entered correctly but sum formula doesn’t work 😢
@@marinavlasova8235 that's rather strange
Nice video..I have refered your video in comment section of other video mentioning how to take care of autoupdate illustrated in above video. However in other video additional information was mention that- how to save in VBA enable format, which is required for this excel sheet to work where color base events to work/triggered.... sharing other video link - th-cam.com/video/8FyyXMC9Jdo/w-d-xo.html
Thank you so much for your feedback and referring my video, I absolutely 💯
Does'nt work for me !!
sum of 0
I don't quite get you?
@@nonoobott8602 What she is trying to tell you is that we have followed your video but once we do the =SumColor on any color then the sum result comes out to 0. Are we missing something?