Count + Sum Cells based on Cell Colour in Excel – How To

By
Hasaan Fazal
-

One of the most sought after feature among heavy Excel users i.e. count or sum cells in Excel based on cell background color. I really wish we had a formula or a feature that does it for us but it isn’t available yet. But we do have the solution if we turn to VBA and write few lines of code.

Count or Sum cells based on color using Excel filter

Yes! We can use Excel filter to do this if we couple it with the appropriate formula. Your usual COUNT or SUM functions won’t help in this case. This is because Excel filter simply hides the result not fulfilling the criteria and both COUNT and SUM functions consider hidden and visible data therefore, we need a function that processes only visible data and ignore the hidden one.

This is where we meet SUBTOTAL function. This approach is especially good for those who want to avoid getting into VBA.

Step 1: For this approach we need the data to have headings. If data does not have headings already then its suggested to have heading inserted for each column at the top by adding a new row or at least for the column containing colored cells.

Step 2: Selected the cells containing headers and go to Data tab > in sort and filter group click Filter button and you will see drop-down arrows added to the heading cells.

Step 3: Move to the last cell of column containing colored cells. You can do that quickly by simply having an active cell within in that column and hitting CTRL+DownArrow. Move one cell down further and put the following formula for count:

=SUBTOTAL(2,B2:B10)

I had my data in range in cells B2 through B10 only that is why formula contains just that.

Step 4: Click the drop-down arrow button of the column containing colored. A menu will appear and in there hover the cursor over filter by color option and select the desired. This will filter the data down to those cells that contain the selected color only.

If you want to sum the values in specific colored cells you need to change the formula a little as following:

=SUBTOTAL(9,B2:B10)

However, this approach is quite limited. As you can process only on one color at a time. If you have more than one color in large data set, and you want to count, sum or average specifically colored data then much better and faster approach is by making a simple UDF in VBA.

Count cells based on color using VBA

For this we need a UDF i.e. user defined function. Following are the steps:

Step 1: Hit ALT+F11 shortcut key to enter visual basic environment

Step 2: Once you are inside visual basic editor go to Insert > module to insert a new module.

Step 3: Double click newly created module and a new blank window will open on the right. Copy the following code and paste it inside empty window:

Function CountColor(color As Range, data_range As Range)
 Dim dRange As Range
 Dim dColor As Long
dColor = color.Interior.ColorIndex
For Each dRange In data_range
 If dRange.Interior.ColorIndex = dColor Then
 CountColor = CountColor + 1
 End If
Next dRange
End Function

Step 4: The above code has now given you a new function to use named CountColor. Just provide the cell address containing the color you want to use as count criteria and then provide the range of data to process.

Following illustration will walk you through the process:

And you can count for multiple colors at once as well:

Understanding VBA code

Lets understand what the code is about and what are we achieving with it.

Line 1:
Function CountColor(color As Range, data_range As Range)

Keyword “Function” tells Excel that we want to initiate UDF with the name “CountColor”. Inside bracket we required two arguments; “color” and “data_range” and both are of type range meaning we have to mention either a cell address or a range of cells. In descriptive manner the formula is following:

CountColor(cell address with the background color you want to use as criteria, range of cells you want to check for color selected and count)

Remember we have defined two variables color and data_range. But we need to tell from where to get the values for these two and what to do with it. So far it is just a disclosure.

Line 2 & 3:
Dim dRange As Range
Dim dColor As Long

Two variables with the name dRange and dColor are defined. These two will be used to process the information given as part of function arguments mentioned inside parenthesis in line 1. I will explain it further in a bit.

Line 4:
dColor = color.Interior.ColorIndex

Remember dColor variable defined in line 2 and 3? and “color” variable mentioned in line 1?

Once user has mentioned the cell address for first argument “color” we want Excel to get the color index or simply the specific color number and store it as a value of variable dColor. Once stored we can use this color index number as a criteria.

Line 5 to 8:
For Each dRange In data_range
If dRange.Interior.ColorIndex = dColor Then
CountColor = CountColor + 1
End If

Remember we defined dRange as variable and has data_range as argument in line? In fifth line we asked Excel to take each cell in range (that user has mentioned for second argument as data_range) and equate it as dRange.

In sixth line we are invoking IF condition statement and we are checking if the color index number of any cell is equal to the color index number of dColor variable (remember we stored a specific color index number in line 4 above) then add “1” to the count. 

Terminate IF statement once all cells are checked going through each element in dRange.

This essentially creates a loop and check each cell mentioned in range and once all cells are checked loop is terminated.

In few words this code is fetching the color index number of cell mentioned by user and then comparing it against the color index numbers of each and every cell in the range specified. The ones that match are counted in and the ones that doesn’t match are ignored. That is how we get the count.

Sum cells based on color using VBA

Now that we understand how code works. We can modify it just a tad bit to have another function that sums the value of cells that fulfill criteria. Have a look at the following code that makes a new UDF that sum values based on cell color:

Function SumColor(color As Range, data_range As Range)
 Dim dRange As Range
 Dim dColor As Long
dColor = color.Interior.ColorIndex
For Each dRange In data_range
 If dRange.Interior.ColorIndex = dColor Then
 SumColor = SumColor + dRange.Value
 End If
Next dRange
End Function

It is exactly the same code as we had for count function with only difference. Instead of adding “1” for each cell matching the criteria, we want excel to take the value of that cell and add it up.

As simple as it can get!

Here is the illustration that shows count and sum of cell values based on color:

116 COMMENTS

  3. Considering your future vacation? Southwest Airlines can help you and your friends and family create unforgettable vacation memories. It’s interesting to note that the majority of travellers frequently seek How do I Get a Human at Southwest Airlines? In order to have the greatest possible flight experience. Tell us everything you know about this expression so we can effectively plan our next vacation.

  4. Great tutorial! ? This article was incredibly helpful in teaching me how to efficiently count and sum cells based on their cell color in Excel.

  5. Awesome Blog !!! Thanks to Admin for sharing the above list. I visited many pages of your Blog. Really your Blog is Awesome. Keep Sharing such good Stories.

  7. We invite you to visit our tasteofgirls.com website, who provides top-quality and reliable models of escort in Lahore is important to us. We will be your forever client when you decide to choose us. We offer several options to you, including in-call or out-call. That sets us apart from the rest. We’re one of the top Escorts available in town. Our overwhelmingly VIP Lahore Escort girls will spend precious time with you, enjoying your romantic date. The best thing is that you will be received like a queen to satisfy your requirements for a reasonable price.

  8. Study in Italy provides a setting that is both rich and diverse for studying human content. Italy, known for its rich historical and cultural legacy, offers a singular location for investigating all facets of human studies. Italian educational institutions provide a wide range of programmes and courses, from history, sociology, and psychology to literature, philosophy, and the arts. Students can immerse themselves in the lively Italian culture, take classes from academics of distinction, and discover priceless lessons about the human condition. For people who are interested in learning about human content, Italy’s institutions offer a meaningful educational experience.

  9. If you are a stranger in Islamabad or local and feeling lonely and want a companion who can make you feel special, then you need not go anywhere because our private Islamabad escort girls are here for you. Always ready. Our escort girls are the best role players and can make you feel special in their own way. Our Islamabad escorts can be like your friend, girlfriend, wife and simple partner. You may be wondering how a girl can play multiple roles and make you feel special then you only need to contact us once and you will see that our escort agency has made it possible.

  11. I Hope You Will Share Such Type Of Impressive Content Again With Us So That We Can Utilize It And Get More Advantage.

  12. Translate text & document files instantly. Accurate translation for individuals and teams. Millions of people translate with DeepL every day.

  13. Great article! I found the information shared here to be highly valuable and informative. The content you created caught my attention. Thank you for shedding light on this topic and encouraging discussions. I look forward to reading more engaging content from you in the future. Keep up the excellent work!. Also Read my informative content on Casting Jewelry

  15. Our Full Body Massagers in Delhi is a premium wellness center dedicated to providing a rejuvenating and relaxing experience. Our team of skilled and trained therapists is committed to promoting wellness and restoring balance to the mind, body, and spirit.

  16. Hello, I am MrX, an SEO, I saw your website which is very amazing, which has allowed many people to comment and help their work, thank you very much

  17. Hello, I am MrX, an SEO, I saw your website which is very amazing, which has allowed many people to comment and help their work, thank you very much

  18. Este artículo está bien escrito, informativo y fácil de entender. Aprecio cómo descompone temas complejos en términos simples. Es un recurso valioso que ha aumentado mi conocimiento.

  19. I Hope You Will Share Such Type Of Impressive Content Again With Us So That We Can Utilize It And Get More Advantage.

  20. I must say. Rarely do I encounter a blog that’? both equall? e?ucat?v? and interesting, ?nd with?ut a d?ubt, ?ou h?ve hit the nail on the head.

  21. Este es un artículo muy bien escrito. Hay mucha información y conocimiento en este artículo. Este es un muy buen artículo y quiero agradecer al escritor. Tus habilidades de escritura son excelentes. Espero que escribas más artículos de este tipo en el futuro.

  22. Great article! I found the information shared here to be highly valuable and informative. The content you created caught my attention. Thank you for shedding light on this topic and encouraging discussions. I look forward to reading more engaging content from you in the future. Keep up the excellent work!. Also Read my informative content on Casting jewelry manufacturer and supplier

  23. Trapstar Clothing is the Official Trapstar Clothing Women’s Store For Real Trapstar UK Fans. Our Trapstar Provides the latest Trapstar® Collection.

  25. David Jones is an email support professional with expertise in resolving email-related issues. He is highly skilled in troubleshooting Outlook notifications not working, ensuring uninterrupted email communication for his clients. With his customer-centric approach and exceptional problem-solving abilities, he provides effective solutions and ensures exceptional customer satisfaction.

  26. Hiranandani Kalyan is a New Launch project in Kalyan by Hiranandani Developers. Hiranandani Kalyan by Hiranandani Developers is located at Kalyan. Hiranandani Kalyan Kalyan to be scheduled to give possession by . Hiranandani Kalyan is spread over . Hiranandani Kalyan is offering 1 BHK, 2 BHK, 3 BHK, of sizes On Request.

    Hiranandani Kalyan is coming with world class facilities including all the necessary amenities. Hiranandani Kalyan is made in a way to provide a comfortable living for residents and based on modern architecture that feels you of having luxurious life style. Hiranandani Kalyan is giving amenities like Power Backup,Lift,Fire Prevention,Security,Parking,CCTV etc. Hiranandani Kalyan by Hiranandani Developers is well designed and located in a place that has a good surrounding, keeping in mind the requirement of our customers. Hiranandani Kalyan is made up of all quality constructions. In Hiranandani Kalyan The interiors are lavishly designed and the outlook of the residence is classy which attracts you towards it.From the floor plan to the ceiling, the apartments are splendidly carved. Hiranandani Kalyan by Hiranandani Developers is built with world class products which make life easier. In Hiranandani Kalyan the wide open areas allow soothing breeze to enter in peace and harmony in your homes and into your lives. In Hiranandani Kalyan Kalyan The dwelling stands in the middle of the most beautiful ambiance and cool running breeze throughout the rooms make Hiranandani Kalyan a perfect comfort zone for residents to live in harmony and peace.

  27. Hello, this is John Harrison. Is your HP printer not printing? Stress not! I am a printer expert with 8 years of experience in fixing printer-related issues. I help clients with printer errors and issues. You can connect with me anytime for instant printer help.

  28. really your blog is to,much better I am impressed by the details that you have on this website and posts like this. thanks

  29. Awesome Blog !!! I visited many pages of your Blog. Really your Blog is Nice. Keep Sharing such good Stories. Thanks.

  30. Thanks for the information you shared. Loved the way you explained everything in this blog.

  32. I discovered your this post while hunting down some related data on website search. It’s a decent post. Keep posting and upgrade the data.

  33. These are actually impressive ideas in concerning blogging. Its great. I high appreciate this post. You’ve nailed it! update this blog with more information.

  37. I am very happy to discover your post as it will become on top in my collection of favorite blogs to visit.

  40. Very nice article, exactly what I needed. Very useful post i really appreciate thanks for sharing such a nice post.

  41. We integrate your Sales data and provide automation By connecting bigcommerce quickbooks integration sales directly into QuickBooks Desktop and Online, you can automate and …

  42. Nice Blog!!! Thanks to Admin for sharing the above list. I visited many pages of your Blog. Really your Blog is Awesome. Keep Sharing such good Stories. Thanks.

  44. I read your blog and learned a lot from it, I was looking for such a blog, and many thanks for sharing this information.

  45. NICE Blog !!! Thanks to Admin for sharing the above list. I visited many pages of your Blog. Really your Blog is Awesome. Keep Sharing such good Stories. Thanks.

  46. I Hope You Will Share Such Type Of Impressive Content Again With Us So That We Can Utilize It And Get More Advantage.

  49. Dimana saja dan kapan saja Anda ingin bermain, Anda dapat secara langsung mengakses situs resmi Bimaspin dan langsung memainkan permainan apa saja yang ingin Anda mainkan.
    Bermain dengan bocoran slot gacor pada situs BimaSpin dan dijamin modal akan membuahkan hasil yaitu keuntungan berlipat ganda hanya dengan menggunakan satu user ID gacor saja. Mudah untuk dimainkan, aman untuk Anda mainkan kapan saja bersama teman teman Anda.

    Agen Slot Gacor BimaSpin saat ini sudah sangat terkenal dimana pun, dan situs BimaSpin sendiri telah menjadi situs yang paling populer bagi masyarakat mulai dari pemain pemula, hingga pemain senior, semuanya sudah sangat mengenal dan sudah menjadi pelanggan setia situs BimaSpin. Gunakan RTP PRAGMATIC untuk menunjang kemenangan Anda ketika bermain, Anda akan dijamin bisa mendapatkan maxwin slot hanya dalam beberapa kali spin saja.

    Cukup dengan minimal deposit saja pun sudah sangat mudah untuk mendapatkan jackpot tertingginya.
    Anda dapat mengunjungi situs resmi Bimaspin.
    Segera daftarkan diri Anda untuk mendapatkan bonus gratis dan spesial pendaftaran baru. Anda akan dijamin bisa mendapatkan maxwin slot hanya dalam beberapa kali spin saja.

  50. Bersedialah untuk menjadi pemenang di situs ANGGUR88 seperti member lainnya, dijamin tidak akan rungkad dan rugi ketika Anda memutuskan untuk bermain di situs ANGGUR88. Jika Anda sedang mencari situs slot online yang mudah untuk dimainkan, aman untuk Anda mainkan kapan saja bersama teman teman Anda. Segera daftarkan diri Anda untuk mendapatkan bonus gratis dan spesial pendaftaran baru.

    Sudah sangat ramai yang memberikan review baik setelah memainkan game di situs ANGGUR88 hingga hari ini, Anda tentu bisa menjadi pemenang berikutnya di situs gacor ANGGUR88. ANGGUR88 telah diakui sebagai slot gacor gampang menang yang mampu memberikan Anda profit besar hanya dalam beberapa kali putaran permainan saja. Segera kunjungi situs resmi ANGGUR88 untuk memperoleh akun pro VIP saat ini. Dijamin modal akan membuahkan hasil yaitu keuntungan berlipat ganda hanya dengan menggunakan satu user ID gacor saja.

    Agen slot ANGGUR88 sangat layak untuk Anda coba mainkan bersama teman Anda, dijamin Anda akan merasakan pengalaman paling menyenangkan setelah bermain di situs ANGGUR88. Spesial slot gacor 88 situs ANGGUR88 bisa Anda dapatkan dengan mudah dan 100% bisa menang, hanya dengan sekali mendaftar saja maka Anda sudah pasti langsung mendapatkan user ID gacor dan mudah mencapai maxwin.

  51. Very good information. Lucky me I ran across your blog by accident (stumbleupon). I’ve bookmarked it for later!

  54. Excellent site you have here.. It’s hard to find high-quality writing like yours these days. I honestly appreciate individuals like you! Take care!!

  56. I am not good at Exel and this tips are really so helpful.

  57. I was browsing for a new jacket on several websites for the forthcoming winter. I looked at various websites but couldn’t settle on one. After a few visits, I found Buffalo Bills Blue Satin Bomber Jacket on Jacket-Hub. I really loved this jacket on these websites, and it’s not enough that we can purchase it in three different styles at the greatest price.

  58. Rapid Assignment Help offers online assignment writing services in UK at affordable prices. Secure top grades with the help of our professional experts. Our assignment help services offers you with quality content and 100% plagiarism free assignments. Browse our more sites like Assignment Help Edinburgh and Assignment Help Nottingham.

  59. This is very good idea and I took help many time from your site and I would like to learn proper formula in excel.

    Thank you so much!

  61. thanks its very helpful

  63. Find Out A Relevant Info On How Can I Use A Fake Name On Cash App
    Are you one of those users who are using a fake name on their Cash App accounts? All you have to do is to have a word with the troubleshooting experts on how Can I Use A Fake Name On Cash App. After clarify your doubts, you should proper utilize the real name of your Cash App account with optimum ease.

  64. Wanna know how to increase cash app withdrawal limit?
    Trying to Increase the Cash App Withdrawal Limit but could not do due to lack of knowledge of the procedure to increase the limit. If you want to increase the cash app withdrawal limit of your account then you have come in the right direction to know about the method to inverse the withdrawal limit of the cash app. to get the way to increase the withdrawal limit you just have to click on this link.

  65. Career Developer is MBBS admisson consultant in patna.Career Developer brings for you the best collages option in India as well as in abroad like Russia, China, Philippine, Ukraine, Belarus, Kyrgyzstan, Nepal, Bangladesh and many more, we are here to give you best carrier option in your respective field, as the Career Developer is the most distinguished leading educational organization.

  66. Our coursework help USA experts have attained a lucid style of writing. They can compile essays in any style of writing if aspirants specify their requirements. Our professionals are highly adaptable. They have a significant eye for writing essays or assignments.

  67. Lodha Group is a real estate company known for its expertise in ensuring best quality and mastery over the design area. Codename Best Deal Wadala project, where life is evergreen is a New Lunch project from the house of Lodha Group properties. Lodha Group properties brings you a new world of live comfortable location of Wadala in Mumbai, where lifestyle at its best phase! Codename Best Deal Wadala offers competitively priced apartments to those looking for a stylish and classy lifestyle. Codename Best Deal Wadala gives high on connectivity and convenience as well and the finest standalone tower. Codename Best Deal Wadala is Conveniently connected by Eastern Free way , Monorail and upcoming Wadala-Thane Metro. Near Codename Best Deal Wadala positioned in close proximity to major IT campuses, your work place could be just a stone’s throw away. Codename Best Deal Wadala is offers to a very good qualities and futures in whole life. such as to its a very good news because Wadala is situated to in heart of Mumbai, where we know about in a good natural thing. Codename Best Deal Wadala is going to offer 2 BHK, 2 BHK Luxury, 3 BHK Luxury spacious apartments integrated with the number of world class amenities that gives the best living structure for the lifestyle with EOI Benefit upto 15 Lacs.

  68. Dotom domain Govandi is project by Dotom realty in Govandi, Mumbai. Dotom domain Govandi offer 1,2 BHK apartments at reasonable price rate. Dotom domain Govandi comes with best class internal and external amenities and with near by places.

  71. Thank you! This is a simple solution that I have been trying to find the answer to for hours! This simple VBA worked great, no knowledge needed!

  72. At VIGNAN IAS, we take immense pride in introducing ourselves as one of the Best IAS Coaching Center in Bangalore for IAS coaching with maximum success ratio

  73. Great work Hassan & i also want learn MS Excel really appreciate. really happy to found pakistani web. Good luck man i am also from pakistan.

  74. Always love to read your blogs as they are easy to understand because of your way to describe the topic.

  77. Your post is very unique and reliable information for all readers to write more on the same topic and share with us your info…Thanks,    

  80. quite an impressive site you have here. as an excel expert i must admit that your knowledge is challenging to me. Thanks

  82. I am a fan of Excel and i must admit that i like the videos so much. Thanks

  84. Thanks, It’s working.. !

  85. Your post is very unique and reliable information for all readers to write more on the same topic and share with us your info…Thanks,

  86. thanks so much for this wonderful piece of information I really love your website and all it web blogs and post I’m going to recommend your website to others it really informative and has good content. GOOD JOB ..

  87. Thanks for giving such information. I will be very help full to play with excel.

  88. Thank you very much:-) you helped us a lot:)

  89. very helpful

  90. thank you very much the information was very much useful and this issue was very much complicated for me and these formulas ease and solve my problems

  91. Thank you for sharing! It really helps!

  92. My issues are resolved on how to count sum cells based on cell colour excel

  93. Hi Hasaan,

    Thanks for your function very easy to use
    My only question is auto updating the user defined formula

    I find the count or sum works all ok but when I change a cell colour in the range the formula will not uodate unless I click into the formula cell and force a recalculation

    Is there something I’m missing?

  94. I’m very concerns because when I close the file then I reopen it was error. Could you tell me why

  95. I greatly appreciate your blog posts! As a Budget Analyst I am constantly exploring ways to automate and streamline my Excel tasks. You have been a great help to that end. Thank you so much!

  96. This post really helps me a lot. Thank You so much.

  99. I have followed this step by step but for some reason it is not calculating

    Values just keep coming up with 0 or #NAME?

    Please advise

  100. Thanks for the sharing such very informative content regarding summing up cells in Excel based on cell background color. I am happy I found this blog!

  101. much obliged to you for the blog. it was incredible help.

    if it’s not too much trouble allude to this site for additional data

  102. Wow.. very very useful post. Keep more like this. Thank you for the wonderful sharing.

  103. Oureducation Coaching Institute our students in safe social and physical environment and provide freedom to go beyond the IAS syllabus that transforms the young mind into future leader.

  104. KEI Hifi store is best online shop in india.you can get Hegel Music Systems, Power Amplifier, Hegel Electronics High End Audio Music System in reasonable prices

  105. This is a great information.Keep blogging this type of post.This type of information is very useful

  106. Your article is very unique and powerful for the new reader.

  107. Ace Pneumatic is a manufacturer of pneumatic chisel, pneumatic cylinder manufacturers, steel rod manufacturers with simple motto of customer satisfaction & quality product.

  108. In the event that you are confronting issue while surfing the web on your program, you can dial our Google Chrome Support +1-855-746-8414.

    It has untold quality and now it is available in the much better brand.Call our Firefox Technical Support +1-855-746-8414 for any brand.

    At the Internet Explorer Technical Support, the specialized arrangement is constantly both subjective and effective by means of the various method for specialized help on Internet Explorer Support Number +1-855-746-8414.

    Most common problem users are facing with Opera Browser are to set up or install Opera Browser. To resolve these problems you need Opera troubleshooting help. Dial the Opera Support Number +1-855-746-8414 anytime.

    On the off chance, if you face technical Issue when using your Safari browser or you are not able to assume the direction of using the browser, do not get tensed. This is because we are there to help you come out of such condition with our absolute Safari Customer Service +1-855-746-8414 Toll-Free.

  109. thank you for the blog. it was great help.
    please refer to this website for further information

  110. Great Information For everyone. Because today’s time everyone is using Microsoft excel. Keep Sharing.

  111. Barry HAW WC ESS MEL AD WB FRE R GEE 6
    Bev HAW GW ESS MEL AD WB FRE R GEE 5
    Dawn HAW GW ESS MEL AD WB FRE R GEE 5
    Dean HAW GW CA GC AD WB FRE NM COL 3
    Grant HAW GW ESS MEL AD WB FRE R COL 4
    Steve HAW WC ESS MEL AD WB FRE R GEE #NAME?

    WIN LOST
    I want to count the number of squares that are correct for each person. In excel i have colored them green.
    I would like the score to automatically increase each time i color another entry with a green background. how can i improve what i have so far.?

  112. very helpful

  113. Interesting idea, though it doesn’t seem to work on conditional formatting, which for me, does not make it that useful. If it could be made to work to detect the color of cells using conditional formatting it would be great to use with pivot tables.

    • Hmmm really interesting. I will have to give it a try and see what works. Seems Interior property needs additional details to get it to work with conditional formatting. Probably “displayformat”. I will update the post once I have the solution for conditional formatting.

      See this is how feedback works 🙂 Thanks for letting us know. I am on it!

      • Hi Hasaan,

        Did you resolve this issue with conditional formatted colored cells?
        Your macro returns 0 on conditional formatted cells.

Comments are closed.