SUM AND COUNT CELLS BASED ON COLOR IN EXCEL (NO VBA)

[@gabech84]

thank you! Easier than VBA, and my workbook doesnt get slow by this :)

[@nonoobott8602]

Thank you for the feedback. Absolutely appreciate!

[@martinelduin2214]

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!

[@nonoobott8602]

Thanks so much for the feedback. I absolutely appreciate and I'm encouraged

[@shumraphael8867]

Totally agreed.

[@DoubleGandP]

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

[@soroushk3432]

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?

[@iuvela]

Absolutely amazing!

[@nonoobott8602]

Thank you so much for your feedback

[@JabulaniMcdonaldNyati]

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

[@nonoobott8602]

Thanks for your feedback. However, I'm not so clear. Could you be more explicit in your question. Thank you

[@cammer46]

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

[@nonoobott8602]

What are you trying to get right please?

[@nonoobott8602]

And yes if you have Macros in your workbook, you need to need to save it as a macro-enabled workbook. That's correct!

[@philipakinfletcher9140]

Awesome training!

[@nonoobott8602]

Thanks for your feedback. Absolutely appreciate

[@sgui8523]

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!

[@nonoobott8602]

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

[@yewchunlee3351]

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?

[@nonoobott8602]

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

[@yewchunlee3351]

@@nonoobott8602 Got it, thanks. It's already good enough for me on this part. ^_^

[@shumraphael8867]

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.

[@shumraphael8867]

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.

[@kushner8460]

How do you get the color index?

[@nonoobott8602]

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

[@beverleylowry5888]

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

[@nonoobott8602]

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

[@samueloluwaseun7496]

Please how do I get the colour index number?

[@marinavlasova8235]

Help! Countifs works, sumifs does not :(

[@nonoobott8602]

Thanks for your feedback. Pls can you be more explicit so I can help

[@marinavlasova8235]

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

[@nonoobott8602]

@@marinavlasova8235 that's rather strange

[@yhenyhenrivera8418]

how did you get the 63?

[@nonoobott8602]

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

[@16eranc]

ok

[@flowerlily3568]

Does'nt work for me !!

[@nirajpdesai]

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 - kzbin.info/www/bejne/bnfcqouDeJ59mtE

[@nonoobott8602]

Thank you so much for your feedback and referring my video, I absolutely 💯

[@abdulrahmanaldahmasi738]

Hi

[@junrelfacturan5955]

sum of 0

[@nonoobott8602]

I don't quite get you?

[@DMSLYRB]

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