• Dp

How to count colored cells in MS Excel

In this tutorial, let's learn How to count colored cells in MS Excel.

Here, we would be using the VBA method and this is the most easiest method to count colored cells in MS excel.


This is the example that I would like to show you now and let us count the number of red and orange colored cells.


First, go to VBA window by pressing Alt+F11 shortcut.


1. You will land into this VBA window now.

2. Click on Insert>> Module to insert a new module.

3. Copy the below code and paste it here in the module.

Function GetColorCount(CountRange As Range, CountColor As Range) 
Dim CountColorValue As Integer 
Dim TotalCount As Integer 
CountColorValue = CountColor.Interior.ColorIndex 
Set rCell = CountRange 
For Each rCell In CountRange   
If rCell.Interior.ColorIndex = CountColorValue Then     
TotalCount = TotalCount + 1   
End If 
Next rCell 
GetColorCount = TotalCount 
End Function

4. Save your excel and close this VBA window and go back to the Excel sheet.


Using this VBA code, we have created a custom function called "Getcolorcount"


And this function can be used like any other function now.


Here, we have two colors red and orange in the sheet.


In J4 Cell, type the formula as =getcolorcount(F5:F38,I4)

here, F4:F38 represents the data which to be counted and

I4 is the background color, what to be counted.


Press enter and you will get the result as 9. You can cross verify the figure whether it is right or not.


Similarly, let us count the red colored cells by typing formula in cell J5 as =getcolorcount(F5:F38,I5) and press enter to see the result as 8.


So friends, this is all about the How to count colored cells in MS Excel.


Watch this video tutorial for better understanding:


If you liked this tutorial, share it with your friends. And also you can follow us on Youtube, Twitter and Facebook. We would love to hear from you, Please do comment, suggest or compliment our work and we shall make it better for you. You can write us at dptutorials15@gmail.com ************************************************************ ★ My Online Tutorials ► https://www.dptutorials.com LEARNING RESOURCES I Recommend: https://www.dptutorials.com/resources Subscribe for more Awesome Tutorials: http://goo.gl/NyAtg2 Support the Channel via shopping: http://ift.tt/2jH38PR Tools that I use for Vlogging: • Laptop: http://fkrt.it/AM9ab_uuuN • Canon 200D Camera: http://fkrt.it/AMz75_uuuN • Benro Tripod: http://fkrt.it/An1lm_uuuN • Microphone: http://fkrt.it/A2RHz_uuuN • Collar Microphone: http://fkrt.it/yL8kdQNNNN • Screen recorder: https://techsmith.pxf.io/2BMjA • Boom Arm Stand: http://fkrt.it/ypUD8QNNNN • Zoom H1 Audio Recorder: http://fkrt.it/ypb7WQNNNN • Harison Softbox Studio Lights: http://fkrt.it/ypCaRQNNNN • Chroma Key Green Screen: http://fkrt.it/A3gnb_uuuN • Background Support Stand: http://fkrt.it/A3cCb_uuuN • Acoustic Foam Background: http://fkrt.it/ypsGdQNNNN • USB RGB LED Strip: http://fkrt.it/A3T_5_uuuN • Wireless Mouse: http://fkrt.it/A3Bqz_uuuN Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support! *********************************************** You Can Connect with Me at: YouTube: www.youtube.com/c/dptutorials G+: http://ift.tt/2kAOpa6 Twitter: https://twitter.com/dptutorials15 Facebook: http://ift.tt/2kfRnDi BlogSpot: http://ift.tt/2kB14dh Follow: www.dptutorials.com www.askplanner.blogspot.com

Support my blog, if you liked

  • YouTube Social  Icon
  • Facebook Social Icon
  • Twitter Social Icon
  • Google+ Social Icon
  • Blogger Social Icon