*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 *WbkCountByColo*r 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

- Count by color:
- 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)

## 730 comments

How do I adjust either the formula or the module to allow me to count cells by color in multiple ranges? Is it possible to add multiple ranges in the current formula in some way or do you have to adjust it somehow? For instance;

=CountCellsByColor((B5:B12,E5:E12),AM5)

Where B5:B12 and E5:E12 are the two ranges I want to count cell in by color to get the sum of cells with the color (ignoring any occurance of the color in columns C-D)

And AM5 is the cell containing the color I want to count in the two ranges.

Hi! I can recommend you create a separate formula for each range of data. Then summarize these results.

=CountCellsByColor(B5:B12,AM5) + CountCellsByColor(E5:E12,AM5)

Hi, great work, thank you.

I am having some issues, I have 29 cells, of which 8 are green fill, through conditional formatting condition ">0".

I have another cell, manually filled with green, as sample colour, as specified in tutorial. When I run the macro, it counts "29" with colour "000000".

Some help please'?

Hi! I can't guess what macro you are using. To count conditionally formatted cells, use a special macro, which is in the article above. Also note that the color code in the sample and in other cells must be the same. To get the color code, there is also a special macro.

Hi, I am using "How to count and sum conditionally formatted cells using VBA macro". I can't use get color code, because in that function: "Note. The functions only work for colors applied manually, and not with conditional formatting."

I am highlighting my 29 cells using condition formatting, condition is numbers above 0, and I use Custom Format and select Green colour.

Then I run the macro, using Sample Cell with same Green colour, but the macro return is: 29 (even if cells are Green and Red color mixed) and color "000000"

Thanks!

Hi! If you read this article carefully, you'll see a paragraph: Count and sum conditionally formatted cells.

Hi, I did read carefully. I don't understand? The cells were conditionally formatted? The condition is, if > 0 then green fill...

Hi! If the SumCountByConditionalFormat macro does not work, then the color of the conditional formatting and Sample Cell are different. Try copying the color of the conditional formatting to the Sample Cell using Format Painter. Use the sample file linked at the end of this article.

This saved my life (or at least a lot of time) this afternoon! Thanks so much!

Hi,

Can I do any of these within a date range?

Hello, this is excellent and extremely handy. Is there an easy way to modify the code so that it functions similarly to COUNTA? I have some cells that are colored but are blank other than that and it would be nice if the function would only count those with data in it. Thanks!

Hi! Your VBA code change request goes beyond the advice we provide on this blog, sorry.

To count colored cells that have values in them, try this formula:

=SUM((DEC2HEX(GetCellColor(A10:A20))<>"FFFFFF")*(A10:A20<>""))

I hope it’ll be helpful.

I’ll try this, thanks!

I'm trying to count cells by color. I've pasted the code and I can see that it calculated the Formula result perfectly in the Function Arguments window. However, when I select OK, the cell returns #VALUE. I have a feeling it's because the range selected has cells that contain "". I used an IF function in the cells to return the value as "" to avoid having 0's fill up my table. Any suggestions?

Hi! The information you provided is not enough to understand your case and give you any advice. What formula are you using? Give an example of your data.

This is a great solution.. Many Thanks for this functions..

These functions are exactly what I was looking for, however all I get is the dreaded #NAME? error.

Install the VBA code and follow all the instructions described in the article above.

Pls, how do I sum a cell with color when it is equal to a particular date?

Which means; Sumifs using the cell color and date as the criteria.

Svetlana,

This is a wonderful function. Thank you!

if I have the same cell layout on several tabs within the same workbook, how could I count the number of colored cells in the work book on Cell A6 for example?

1 workbook, 100 sheets.

Count how many each of the cells are colored in the same cell.

sheet1 A5

Sheet2 A5

sheet 3 A5

etc

have 2 colored cells.

Sheet1 C50

Sheet2 C50

Sheet3 C50

etc

have 50 colored cells.

Hi! Unfortunately, the user-defined Excel functions do not support 3-D reference. You need to specify each sheet separately.

Hi,

The SumCellsByColor worked great in a local instance of Excel on my desktop, but when I move the script to Microsoft365 Excel, I'm getting 125 errors. I'm honestly not sure where to begin.

Examples of errors:

Line #2: ';' expected.

Function SumCellsByColor(data_range As Range, cell_color As Range

Line #2: ',' expected.

Function SumCellsByColor(data_range As Range, cell_color As Range

Line #2: ',' expected.

Function SumCellsByColor(data_range As Range, cell_color As Range

Line #2: ',' expected.

Function SumCellsByColor(data_range As Range, cell_color As Range

Hi! Check which characters you are using as delimiters. Download the sample file linked at the end of this article. You can use this file or copy the VBA code from it.

Hi, and thanks for providing the code. For some reason my Excel does not recognize the funiction. When I type =CountCellsByColor(C4:E5, G4) Excel returns a popwindow telling: "There is a problem. Do you try to put in a function? When the you start with = Excel thinks it is a function". So I assume I have whatever wrong setting making my Excel unable to recognize the function. Can you help me?? Thanks in advance.

Hi,

I am trying to count 6 different cell colors into 1 number (red, yellow, pink, blue, green,purple, orange=7 ). Can I assign multiple values to get the desired results? Also is there a way that each counted cell is counted as .5? I'm trying to track supervision on each staff by 6 different supervisors and each colored cell represents 1/2 hour.

Hello! You can use a separate CountCellsByColor function for each color and then sum the 7 results. If necessary, you can multiply the result of the CountCellsByColor function by 0.5

Thank you!

Work well for me.

=SumCellsByFontColor

tks so much

I need help with a formula that I'm needing for a new work spreadsheet that I'm trying to create to keep track of which employees have completed their yearly certifications. I have employee names in cells the first 25 cells of columns B through W (they are broken down in a specifc way so I can't add or combine more in a column). As each employee Right now I am using a CountA formula that I'm using showing the total of employees (= counta (B2:W25) but as I highlight the employee names when I get their confirmations I want that cell subtracted from the total so I can see at a glance how many employees are left. I read about the count by cell color formula and that would work but I can't figure out how to combine the two to get a total. In my mine I thought I could put =counta(b2:w25)-CountCellsByColor(b2:w25) and that would give me my total but it doesn't. Please help.

Thanks in advance for any and all help!

P.S. I have Ablebits downloaded on both my personal and work computers and it's helped me so much. Glad I downloaded and bought it.

Hello! In the CountCellsByColor function, specify the second argument – Cell_color. This is a reference to the cell with the target fill color.

For example,

=COUNTA(B2:W25)-CountCellsByColor(B2:W25,A1)

Hope this is what you need.

Countcellsbycolor worked perfectly thanks very much. Is there a way to modify the code so it counts the number of rows of a colour within a certain range? I.e. all rows from A10:A50 that are coloured the same as B2.

Hi! The first argument of the CountCellsByColor custom function is the range in which you want to count cells.

How to get the totals updated when a color is changed?

Hi! Custom Excel functions are recalculated automatically, just like regular functions.

CountCellsByColor Function Not Working, When I use this function then result are 0, please solve this problem..

The function works. Describe the problem in detail and we will try to help.

I want to count individually cells. For exp- i have hugh excel data and i want to count how many green cell & how many pink cell in table, but the proble are when i used your function then result are 0 every time .

That's means - green cells -- how many

pink cells -- how many

pleace help how to do.

Unfortunately, I can't work with your data. Try to use the sample file linked at the end of the article. Use the CountCellsByColor function, don't forget to specify the cell with the desired color.

To count conditionally formatted cells, use VBA macro, as described above.

I modifed the code to count the celss with conditional formating as below but it is giving value error

Function CountCellsByColor(data_range As Range, cell_color As Range) As Long

Dim indRefColor As Long

Dim indCurCell As Long

Dim cellCurrent As Range

Dim cntRes As Long

Dim cntCells As Long

Application.Volatile

cntRes = 0

cntCells = data_range.CountLarge

indRefColor = cell_color.Cells(1, 1).DisplayFormat.Interior.Color

For indCurCell = 1 To (cntCells)

If indRefColor = data_range(indCurCell).DisplayFormat.Interior.Color Then

cntRes = cntRes + 1

End If

Next

CountCellsByColor = cntRes

End Function

Counting of colored cells does not work on cell ranges that are conditionally formatted.

Hi! Pay attention to the following paragraph of the article above: Count and sum conditionally formatted cells.

When I change a cell's color, will the totals be updated instantly?

This is very helpful!

Is there a way to combine CountCellsByColor and CountCellsByFontColor? I have a set of data that has 5 different colors and 2 different font colors and trying to get 10 unique values from it. I was thinking it would incorporate CountIfs but my tests haven't been working.

Thanks in advance!

Hi! Unfortunately, it is not possible to combine the two custom functions. A new custom function must be created.

Hi, great extention and very helpful, thanks. But if I need to pick just several cells instead of a certain range I can't understand how to.

for example instead of

=SumCellsByColor(D18:F21;O20)

I need to summm only four cells, like

=SumCellsByColor(E18+F19+E20+D19;O20)

but it doesn't work. What am I doing wrong?

Hi! Use either a single cell or a range of cells in the SumCellsByColor function. For example,

SumCellsByColor(E18)+SumCellsByColor(F19)

This is great stuff! Thank you. Got a question, though. Two columns side-by-side. Each column has cells that are colored either red or green. Trying to count the cells where it's red in column 1 and green in column 2, or green in column 1 and red in column 2. Also need to count the ones that are red in both columns and green in both columns. Is there a way to do this?

Hi!

This formula can count how many cells in the both columns have a value equal to cell A2 and are colored red.

=MIN(SUM((GetCellColor($A$2:$A$20)=255)*($A$2:$A$20=A2)), SUM((GetCellColor($B$2:$B$20)=255)*($B$2:$B$20=A2)))

I'm not exactly imagining your data, so try the formula below as well. It compares the cells in each row in pairs.

=SUM((GetCellColor($A$2:$A$20)=255) * (A2:A20=B2:B20) * (GetCellColor($B$2:$B$20)=255))

Use the custom functions from the article above.

The Count and sum by color across entire workbook was well explained and simple to embed into my Workbook.

Thankyou so much!!

Hello , I am using MICROSOFT OFFICE LTSC PROFESSIONAL PLUS 2021 and I follow the same formula and it still not work #NAME,

Then I download the excel sample and copied the entire data with the formula and the result is the same #NAME

Any help?

Hello!

Have you saved the file where you added the code as a Macro-Enabled Workbook (.xlsm)? If the issue remains unresolved, please try downloading our sample workbook provided at the end of this article, unblocking the macro (Microsoft automatically blocks macros in any files downloaded from the internet), and see if the function works in that particular workbook.

Hi Fernando,

From the sample workbook, you need to copy not only the data/formulas but also the function's VBA code. Have you done that?

I have the following problem with respect to "Count on conditionally formatted cells"

1. For my data I need to filter it first on 1st column lets say with INDEX or FILTER functions

2. Range received as output of Step 1 needs to be used as Input of "Count on conditionally formatted cells"

How to do this ?

Example : lets take the same sample table as you showed but add another column before the 1st one as category

Say choose 2 categories as "Fruit1" and "Fruit2" and place these category against each of 2nd column data

Now I need separate "Count on conditionally formatted cells" for "Fruit1" category and separate for "Fruit2" category ...

Pls help on how to achieve this ...

Hi! Unfortunately, to solve your problem you need to write a new VBA macro.

hmm... I am not familiar with VBA.. Is someone could help with this ?

how to add the countecellby color in excel and not run the VBA.

thanks !

Hello! First of all, I really appreciate all the tips and tools; they are extremely helpful. So here is my problem. I added the Macro for CountCellsByColor and saved. Wrote for formula in the associated cell reference and it calculated, but the number it calculates is not correct. for example, I am approved for 10 staff, I have 9 currently and one potential new hire. So, instead of it count 9, it is calculating 14, what I am doing wrong?

Hi!

I can't guess which formula you are using and I can't see your data.

Using CountCellsByColor and SumByColor and they work GREAT! Thank you!!

How would I then get the MAX from the SumByColor?

I have tried a few things and none work, so far. Unfortunately, I do not code very well...

• Looking for a MaxByColor routine.

Any help would be most appreciated!

Hey all! Thanks so much for posting this code. Is it possible to output conditionally formatted color count to a cell so that I can sum cells? I was able to use the macro to do it perfectly, but I need to be able to separate counts and then sum for a total. Is there any way to do this?

Nevermind, I found the answer below from Alexander and have used this successfully. Thank you Ablebits team for this help!!!

I can't get this to work to save my life. I'm trying to get it to count cells by color, used your formula and it if throws #NAME? in the place of "CountCellsByColor".

Do what is written in the first paragraph of this article.

Same problem. What is need to be followed in the 1st paragraph?

Hi!

Read carefully. Add this user-defined function to your workbook as described in these instructions.

done this but still getting the #name? error

Hi!

#NAME? error means that either you got the function name wrong, or the function is not present in your version of Excel.

Having the same issue. Copied and pasted the CountCellsByColor VBA code. Saved workbook as .xlsm. Still getting the "#NAME?" error when trying to use the function.

Hi! I can't see how you do this and what you did wrong. Check where you pasted the code, read the instructions again carefully. You can download the example file from the link at the end of this article. Perhaps this instruction will be helpful: Why custom functions are not available.

Hi Steven,

Did you save the file where you've added the code as a Macro-Enabled Workbook (.xlsm)?

If the error persists, please try to download our sample workbook at the end of this article, unblock the macro (by default Microsoft blocks macros in all files downloaded from the internet), and see if the function works in that workbook.