In this article, you will learn new effective approaches to summing and counting cells in Excel by color. These solutions work for cells colored manually and with conditional formatting in all versions of Excel 2010 through Excel 365.
Even though Microsoft Excel has a variety of functions for different purposes, none can calculate cells based on their color. Aside from third-party tools, there is only one efficient solution - create your own functions. If you know very little about user-defined functions or have never heard of this term before, don't panic. The functions are already written and tested by us. All you need to do is to insert them in your workbook :)
How to count cells by color in Excel
Below, you can see the codes of two custom functions (technically, these are called user-defined functions or UDF). The first one is purposed for counting cells with a specific fill color and the other - font color. Both are written by Alex, one of our best Excel gurus.
Once the functions are added to your workbook, they will do all work behind the scenes, and you can use them in the usual way, just like any other native Excel function. From the end-user perspective, the functions have the following look.
Count cells by fill color
To count cells with a particular background color, this is the function to use:
Where:
- Data_range is a range in which to count cells.
- Cell_color is a reference to the cell with the target fill color.
To count cells of a specific color in a given range, carry out these steps:
- Insert the code of the CountCellsByColor function in your workbook.
- In a cell where you want the result to appear, start typing the formula: =CountCellsByColor(
- For the first argument, enter the range in which you want to count colored cells.
- For the second argument, supply the cell with the target color.
- Press the Enterkey. Done!
For example, to find out how many cells in range B3:F24 have the same color as H3, the formula is:
=CountCellsByColor(B3:F24, H3)
In our sample dataset, the cells with values less than 150 are colored in yellow, and the cells with values higher than 350 in green. The function gets both counts with ease:
Count cells by font color
In case your cell values have different font colors, you can count them using this function:
Where:
- Data_range is a range in which to count cells.
- Font_color is a reference to the cell with the sample font color.
For example, to get the number of cells in B3:F24 whose values have the same font color as H3, the formula is:
=CountCellsByFontColor(B3:F24, H3)
Tip. If you'd like to name the functions differently, feel free to change the names directly in the code.
How to sum by color in Excel
To sum colored values, add the following two functions to your workbook. As with the previous example, the first one handles fill color and the other - font color.
Sum values by cell color
To sum by fill color in Excel, this is function to use:
Where:
- Data_range is a range in which to sum values.
- Cell_color is a reference to the cell with the fill color of interest.
For example, to add up the values of all cells in B3:F24 that are shaded with the same color as H3, the formula is:
=SumCellsByColor(B3:F24, H3)
Sum values by font color
To sum numeric values with a specific font color, use this function:
Where:
- Data_range is a range in which to sum cells.
- Font_color is a reference to the cell with the target font color.
For instance, to add up all the values in cells B3:F24 with the same font color as the value in H3, the formula is:
=SumCellsByFontColor(B3:F24, H3)
Count and sum by color across entire workbook
To count and sum cells of a certain color in all sheets of a given workbook, we created two separate functions, which are named WbkCountByColor and WbkSumByColor, respectively. Here comes the code:
Note. To make the functions' code more compact, we refer to the two previously discussed functions that count and sum within a specified range. So, for the "workbook functions" to work, be sure to add the code of the CountCellsByColor and SumCellsByColor functions to your Excel too.
How to count colored cells in entire workbook
To find out how many cells of a particular color there are in all sheets of a given workbook, use this function:
The function takes just one argument - a reference to any cell filled with the color of interest. So, a real-life formula may look something like this:
=WbkCountByColor(A1)
Where A1 is the cell with the sample fill color.
How to sum colored cells in whole workbook
To get a total of values in all cells of the current workbook highlighted with a particular color, use this function:
Assuming the target color is in cell B1, the formula takes this form:
=WbkSumByColor(B1)
Count and sum conditionally formatted cells
The custom functions for adding up and counting color-coded cells are really nice, aren't they? The problem is that they do not work for cells colored with conditional formatting, alas :(
To handle conditional formatting, we have written a different code (kudos to Alex again!). It works well with both preset formats and custom formula-based rules. Contrasting with the previous examples, this code is a macro, not a function. The macro counts and sums conditionally formatted cells by fill color. Please insert it in your VBA Editor, and then follow the below instructions.
How to count and sum conditionally formatted cells using VBA macro
With the macro's code inserted in your Excel, this is what you need to do:
- Select one or more ranges where you want to count and sum colored cells. Make sure the selected range(s) contains numerical data.
- Press Alt + F8, select the SumCountByConditionalFormat macro in the list, and click Run.
- A small dialog box will pop asking you to select a cell with the sample color. Do this and click OK.
For this example, we used the inbuilt Highlight Cell Rules and got the following results:
- Count (12) the number of cells in range B2:E22 with the same color as G3.
- Sum (1512) is the sum of values in cells formatted with Light Red Fill.
- Color is a hexadecimal color code of the sample cell.
Tip. The sample workbook with the SumCountByConditionalFormat macro is available for download at the end of this post.
How to get cell color in Excel
If you need (or are curious) to know the color of a specific cell (fill or font color), add the following user-defined functions to your Excel. It returns ColorIndex as a decimal number.
Note. The functions only work for colors applied manually, and not with conditional formatting.
Get fill color of a cell
To return a decimal code of the color a given cell is highlighted with, make use of this function:
For example, to get the color of cell A2, the formula is:
=GetCellColor(A2)
Get font color of a cell
To get a font color of a cell, use an analogous function:
For instance, to find the font color of cell E2, the formula is:
=GetFontColor(E2)
Get hexadecimal color code of a cell
To convert a decimal color index returned by our custom functions into a hexadecimal color code, make use of Excel's native DEC2HEX function.
For example:
="#"&DEC2HEX(GetCellColor(A2))
="#"&DEC2HEX(GetFontColor(E2))
How to insert VBA code in your workbook
To add the function's or macro's code to your Excel, move on with these 4 steps:
- In your workbook, press Alt + F11 to open Visual Basic Editor.
- In the left pane, right-click on the workbook name, and then choose Insert > Module from the context menu.
- In the Code window, insert the code of the desired function(s):
- Count by color: CountCellsByColor and CountCellsByFontColor functions
- Sum by color: SumCellsByColor and SumCellsByFontColor functions
- Count and sum colored cells in whole workbook: WbkCountByColor and WbkSumByColor functions
- Count and sum conditionally formatted cells: SumCountByConditionalFormat function
- Get color code: GetCellColor and GetFontColor functions
- Save your file as Macro-Enabled Workbook (.xlsm).
If you are not very comfortable with VBA, you can find the detailed step-by-step instructions and a handful of useful tips in this tutorial: How to insert and run VBA code in Excel.
How to get custom functions to update
When summing and counting color-coded cells in Excel, please keep in mind that your formulas won't recalculate automatically after coloring a few more cells or changing existing colors. Please don't be angry with us, this is not a bug in our code :)
The point is that changing cell color in Excel does not trigger worksheet recalculation. To get the formulas to update, press either F9 to recalculate all open workbooks or Shift + F9 to recalculate only the active sheet. Or just place the cursor into any cell and press F2, and then hit Enter. For more information, please see How to force recalculation in Excel.
Fastest way to calculate colored cells in Excel
If you do not want to waste time tinkering with VBA codes, I'm happy to introduce you to our very simple but powerful Count & Sum by Color tool. Together with 70+ other time-saving add-ins, it is included with Ultimate Suite for Excel.
Once installed, you will find it on the Ablebits Tools tab of your Excel ribbon:
And here is a short summary of what the Count & Sum by Color add-in can do:
- Count and sum cells by color in all versions of Excel 2016 - Excel 365.
- Find average, maximum and minimum values in the colored cells.
- Handle cells colored manually and with conditional formatting.
- Paste the results anywhere in a worksheet as values or formulas.
Sum and count cells by one color
Selecting the Sum & Count by One Color option will open the following pane in the left part of your worksheet. You specify the source range and sample cell, then then click Calculate.
The result will appear on the pane straight away! No macros, no formulas, no pain :)
Apart from count and sum, the add-in also shows Average, Max and Min for colored numbers. To insert a particular value in the sheet, click the Paste button next to it. Or click Paste All to have all the results inserted at once:
Count and sum all colored cells at once
To handle all colored cells at a time, choose the Sum & Count by All Color option. Basically, it works in the same way, except that instead of color, you choose the function to calculate.
Tip. To have the results inserted in the worksheet as formulas (custom functions), check the corresponding box at the bottom of the pane.
Well, calculating colored cells in Excel is pretty easy, isn't it? Of course, if you have that little gem that makes the magic happen :) Curious to see how our add-in will cope with your colored cells? The download link is right below.
Available downloads
Sum and count by color in Excel - examples (.xlsm file)
Ultimate Suite 14-day fully-functional version (.exe file)
820 comments
I'm getting a syntax error on "Dim indRefColor As Long".
As do I.
"Compile error:
Syntax error"
I copy-pasted the code in to my own document and I still get that error. Doesn't get it with the downloaded example document, and can't figure out what the difference is!
Hi Christopher / Anton, I believe the code in this page had some syntax issues. Please download the sample document from this page. Go to VBA module and copy-paste the code to your sheet.
Hi
Your code works perfectly - thank you. I understand that you can't create a function that will count cells for all types of conditional formatting and output the results to a cell. But if you were to constrain so it assumes one kind of conditional formatting, would that be possible? I only ever use conditional formatting based on cell contents or formulas and I can usually choose either. Would it be possible to create a function that worked with one of those types?
Thanks
Paul
Hi Svetlana,
Wonderful code, working perfectly! I just found one issue - when I delete something or leave an empty cell within the region it is still counted by CountCellsByFontColor.
Any idea how to overcome this? How can I count only non-empty cells based on the font color
Thanks,
Hi,
Thank you for the codes.
I have used the code for the count by conditionally formatted color, and it works. But rather than the information being shown in a message box, I want the information to appear on a cell. Is this possible? Can I amend the code in a way to accommodate this?
How were you able to count by conditionally formatted colour cells? I've been struggling with me returns!
Hello, Khalid.
It looks like our Count and Sum by Color add-in can help you with your task. The tool can help you sum and counts cells by font or background color in a few clicks. If you want to see how the Count and Sum by Color add-in works, feel free to install the fully functional 14-day trial version of Ultimate Suite that contains all our products for Excel (70+ add-ins) using this direct download link.
After installation you can find the add-in in the Calculate group under the Ablebits Tools tab. If you need the instructions how to use the add-in, you can find them on its help page.
Hi, I would like to count cell color of individual worksheet on a master sheet. Each worksheet must be separate.
Doesn't work. Just keep saying 0. followed everything, word for word. The file is even saved as macro ready. Even played with the formatting to match what I was looking for. Nada. I spent over an hour trying to figure out why. I went as far as using a new workbook completely empty starting from scratch to see where I went wrong. Still nothing. I can't figure this out. I'll keep looking online.
super helpful and very quick to apply! Thanks.
Good article.....Keep going
The count cells by conditional format is fantastic. I have been searching the web for over a year for a good solution. I have another twist to this. I have a very large data set that should be unique numbers. I can set a conditional format to look for duplicates but then I need to find them, if they exist. Is it possible to adapt the code to find the first cell that is conditionally formatted (if there is one) and then use this cell to set the format to count possible additional entries in the entire dataset?
So legit I actually took a moment to thank you for this little piece. Just what I needed!
i want to know how to autocount all the numbers insid the color cells
superb
Function works great - just not on cells using conditional formation.
Is there way to count cells by colour if the colour is assigned by conditional formatting?
Very helpful, great instruction!! Thank you.
functions are not working on conditional formation
I am having the same issue
Is there a way to use the countcellsbycolor function on a filtered data set? (excluding the hidden data)
Try subtotal function.
Hi,
I have data where i need to insert sum formula for perticular font coloure rows to sum its above data till the coloure change.
please help me with suitable macro or formula.
Very helpful. thank you.
Ahmed; taken from the article
"Note: If after applying the above mentioned VBA code you would need to color a few more cells manually, the sum and count of the colored cells won't get recalculated automatically to reflect the changes... simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated. The same applies to the other macros you will find further in this article."
frist of all thank u for this good data
but if i change any color in range it doesn’t be counted and the result didn’t updated? i want to count cells by color even i change the color in the range