SUM and COUNT by Cell Colour in Excel -- WITHOUT VBA!
Вставка
- Опубліковано 25 лип 2024
- In this Excel tutorial we will learn how to SUM and COUNT cells based on the background fill colour of the cell WITHOUT using VBA.
VBA is by far the most flexible way to complete the task BUT, there are other methods we can use if we want a simpler resolution.
The four methods we will explore in this video are:
- Find
- The SUBTOTAL function + Filters
- The AGGREGATE function + Filters
- GET.CELL with a macro
#excel #countbycellcolor #sumbycellcolor #microsoft365
********************************
CREDITS:
GET.CELL method shown in this video:
Original video showing this method by Up4Excel. See original video here ⬇️⬇️
• Uncover Hidden Excel P...
GET.CELL full list of arguments posted by user PaddyD on the MrExcel forum. See post here ⬇️⬇️
www.mrexcel.com/board/threads....
********************************
🔗LINKS to related videos
🔢5 Ways to COUNT in Excel
• 🖐️5 Ways to COUNT in E...
🧮Get to Grips with COUNTIFS and SUMIFS
• Get to Grips with COUN...
************************************************
🤝Let's CONNECT on social:
➡️TikTok: / cellmates_
➡️Instagram: / cellmatestr. .
➡️LinkedIn: / deborahas. .
➡️Twitter: / cellmates_
➡️Facebook: / cellmates
I stumbled upon your video and I must say this is a game changer for me…especially the calculate now button! Thank you so much!!!
Interesting solutions and workaround Deb.
Brilliant! Thank you!
thank you ,
it was really helpful
It's an eyedropper what you're looking for. Thanks for the video.
Haha my brain blanked on eye dropper and all I could remember was a pipette! 😂
Thank you for this wonderful video. Is it possible to SUM by cell color using the "Get.Cell" command?
Nice to see you again.
Thanks! It’s been a while!
Great thanks
Great video Deb. 👍
A little warning about the GET.CELL method.
I believe the workbook needs to be saved as .a xlsm file type. Which means other users will need to unblock or enable the file before it will operate correctly.
GET.CELL is an Excel 4 Macro and it is no longer supported. So Microsoft could remove the functionality without any warning if they wanted to (unlikely, but possible).
Yep it needs to be saved as a macro enabled spreadsheet! Good knowledge, thanks for the tip Mark! :)
Hello, I keep getting #NAME? when i do the +now0+0, is there anyway to fix this?
Hi. Loving your tutorials Deb. I have a spreadsheet with coloured cells and values- to identify how much is being spent on each person for Christmas 🎄!! Each colour represents a different person. Can these values be filtered by colour and the values totalled- so at a glance one can see the totals for each person?
Many thanks
Mark
Hi , thank you for this interesting video, I'm trying to do it with Sumif, but not working. what would be the formula?
Hello, you can't do it with SUMIF which is why I haven't shown that method in the video
can you provide commands for macbooks?
Badass channel name.
Is it possible to do it by the color of the text in cell?
Hey Deb, did you leave Simon or is this just just a side hustle?
Hello, I'm a freelancer. I don't work for SimonSez IT but I do freelance work for them as well as many other clients.
I get the same "name" error
but how do you sum by colour?
There’s an annotation in the video. Method is the same. Use SUBTOTAL or AGGREGATE but select SUM from the list instead of COUNT.
This video is only for people who are proficient with excel. It’s not for the massive amount of people who are not. If views are important to you, you should make it for people who have never used excel or almost never use it, which is almost everyone. For that matter, you should make EVERY video from that point of view.
Hello, I complete disagree with you. Not every video is for everyone. I post a complete mix of very basic to more advanced videos. This video ISN’T aimed at those with zero knowledge of Excel.
All of my videos are tagged Basic, Intermediate or Advanced. You can see it in the thumbnail. This one says ‘Excel Advanced’ in the corner.
Not every video can start with a recap of the most basic concepts.
If views on UA-cam were that important to me, I’d post more than 1 video a month. 🤣
If you are looking to learn Excel from scratch, I would suggest a Beginners training course that moves logically through the content. Thanks!
So Much STUPID distractions. Disaster.