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:

48 COMMENTS

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

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

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

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

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

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

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

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

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

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

  11. Thank you for sharing! It really helps!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here