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

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 ⬇️⬇️

GET.CELL full list of arguments posted by user PaddyD on the MrExcel forum. See post here ⬇️⬇️

********************************

🔗LINKS to related videos

🔢5 Ways to COUNT in Excel

🧮Get to Grips with COUNTIFS and SUMIFS

************************************************

🤝Let's CONNECT on social:

[jbyrd]

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

[charlesxix]

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

[GrainneDuggan_Excel]

Interesting solutions and workaround Deb.

[ExcelOffTheGrid]

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

[mzportr]

Thank you for this wonderful video. Is it possible to SUM by cell color using the "Get.Cell" command?

[mamamtl]

You can use Subtotal with 100 plus the function (eg. 102 =count) to ignore hidden. Instead of aggregate.

[godofthecorn]

Question for you! If I have cells of a specific color that are in bultiple different rows and different columns, will this still work? or does it need to be a nice and neat column like your example in the video?

[marktaylor]

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

[shefaliaggarwal]

Hlo mam
I have using vba code but my excel file is very slow while working on it

[kanikabhatia]

can you provide commands for macbooks?

[miladyacoub]

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

[harmony]

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

[ges]

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

[svpst]

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

[jalins]

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.