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

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:

Teaching professional business subjects to the students of FIA, ACCA, CIMA, CA etc. He also found ACCA LIVE which is Pakistan's first portal to provide online classes and distance learning solutions to FIA/ACCA students. At PakAccountants.com he is busy making study material for different qualifications. Beside writing articles he answers questions asked using ASK TUTOR!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

36. Very good information. Lucky me I ran across your blog by accident (stumbleupon). Iâ€™ve bookmarked it for later!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

53. 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, Â Â Â

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

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

56. Thanks, It’s working.. !

57. 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,

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

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

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

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

63. Thank you for sharing! It really helps!

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

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

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

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

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

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

Values just keep coming up with 0 or #NAME?

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

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

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

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

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

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

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

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

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

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

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

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