SUM and COUNT by Cell Colour in Excel -- WITHOUT VBA!

Подписаться 4,7 тыс.

Просмотров 70 тыс.

50% 1

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

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 26

@GrainneDuggan_Excel 10 месяцев назад

Interesting solutions and workaround Deb.

@jbyrd6730 5 месяцев назад

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

@RFHewitt22 4 месяца назад

Brilliant! Thank you!

@sajjadabouei6721 2 месяца назад

thank you , it was really helpful

@Mr3213215 10 месяцев назад

Nice to see you again.

@cellmatestraining 10 месяцев назад

Thanks! It’s been a while!

@charlesxix 10 месяцев назад

It's an eyedropper what you're looking for. Thanks for the video.

@cellmatestraining 10 месяцев назад

Haha my brain blanked on eye dropper and all I could remember was a pipette! 😂

@hakkimabdula 8 месяцев назад

Great thanks

@Noob-ng1of 3 месяца назад

Badass channel name.

@ExcelOffTheGrid 10 месяцев назад

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).

@cellmatestraining 10 месяцев назад

Yep it needs to be saved as a macro enabled spreadsheet! Good knowledge, thanks for the tip Mark! :)

@harmony1547 Месяц назад

Hello, I keep getting #NAME? when i do the +now0+0, is there anyway to fix this?

@marktaylor299 7 месяцев назад

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

@miladyacoub8013 10 месяцев назад

Hi , thank you for this interesting video, I'm trying to do it with Sumif, but not working. what would be the formula?

@cellmatestraining 7 месяцев назад

Hello, you can't do it with SUMIF which is why I haven't shown that method in the video

@kanikabhatia4705 6 месяцев назад

can you provide commands for macbooks?

@ges05 Месяц назад

Is it possible to do it by the color of the text in cell?

@svpst1 7 месяцев назад

Hey Deb, did you leave Simon or is this just just a side hustle?

@cellmatestraining 7 месяцев назад

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.

@vannyr6114 Месяц назад

I get the same "name" error

@gabech84 5 месяцев назад

but how do you sum by colour?

@cellmatestraining 5 месяцев назад

There’s an annotation in the video. Method is the same. Use SUBTOTAL or AGGREGATE but select SUM from the list instead of COUNT.

@jalins6 3 месяца назад

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.

@cellmatestraining 3 месяца назад

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 RU-vid 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!