*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)

## 799 comments

Can you tell me something...

How to count names in rows and don't count if they are in red color.

In case I have a table with planner

There are names in hours for work. If someone is missing marks field with red.

and i want to have column with total visits.. without missings

Hi! Count the number of all names in the rows and subtract the number of names that are in red color.

Useful thank you. However when using CountCellsByColor I get an error that says "Value!" the only thing I see is excel automatically changes the function to COUNTCellsByColor. Otherwise straightforward but doesn't work. :(

Hi! Excel takes the name of the custom function from the code you set up in your workbook. I am not able to verify how you did this. Follow the instructions carefully. You can also use the sample file linked at the end of this article.

Hello!

I followed the steps for count cells by conditional formatting and it worked great! Thank you very much for that.

I had a follow up question:

I get the count in a dialogue box. Is there anyway to get that in a cell instead?

Thank you.

Hi! To do this, you need to modify the macro code and specify in it the cell to which you want to write the resulting value. You can change the address of the cell only if you change the code of the macro. You can try to do this on your own.

Hi!

Is anyone finding that when you change the color (but not the data) the calculation does not automatically update? I have Calculations Options as Automatic, but it won't update unless I hit Calculate Now.

Hi! Excel automatically recalculates formulas when you change the value in a cell or change the formula. Changing the cell format does not change the value, so no automatic calculation is executed. Read more: Excel calculations: automatic, manual, iterative.

CountCellsByColor and CountCellsByFontColor are just what I needed.

Had to add this code as in 6,000 rows with many irregular fill colours applied, I couldn't easily find the cells in one column that were "" or blank, but had a colour (I'm in the UK) fill or text format applied. The below addition would have saved hours trying to find the subtotal errors for various filling colours:

For Each cell current In data_range

If cellCurrent = "" Then 'if typed content is blank

GoTo Skip

End If

If indRefColor = cellCurrent.Interior.Color Then

cntRes = cntRes + 1

End If

Skip:

Next cellCurrent

how do you adjust the "Count and sum conditionally formatted cells" coding so that it can applied for an individual row or column?

Hi! Your problem is not very clear. When using a function SumCellsByColor, you have to specify the cells, row, or column in the formula. When using a macro, you have to select the desired range, row, or column with the mouse. Please read the above article carefully.

Is it possible in Excel for Web (M365) to count cells by background colour within a specific date range? I am told that I cannot access VBA online....

for example I want on a second sheet to have a reference to how may fields in A:A on the first sheet are Green and fall between Jan 1st and Jan 31st. I then want to repeat this operation for Feb, March etc.

So would look like:

Month Green NotGreen

Jan 12 20

Feb 20 10

Mar 10 5

Hi! In Excel for Web, you cannot use VBA macros and user-defined functions.

Thank you. This is the best I've seen online so far.

I have a waterfall which is conditionally formatted. The msg box VBA produced the right aggregation for one row. However, I have a waterfall and would like to return aggregated values for each row based on the conditionally formatted color? [in this case grey...(the VBA msg box color result was D9D9D9)]

How can I get the sum results from the msg box, but as an output from a function? For example, in the data set below summing from left to right, the starting points would be 9990, 2003, 840, 569. I have over a 100 rows currently which expands monthly. The aggregations for each row is precedent for other calculations within that row. I hope you can help. (fingers crossed)

11/1/2023 6,693 9,181 2,672 831 569 345 249 171 134 94 66 53 43 32 21 21 20 10 8 8 3 10 6 (3) 2 (5) 0 0 0 0 0 0

12/1/2023 6,228 8,444 1,713 840 520 316 228 156 123 86 60 49 39 29 20 19 18 9 7 7 3 9 5 (2) 2 (5) 0 0 0 0 0 0

1/1/2024 5,142 8,686 2,003 821 509 309 222 153 120 84 59 48 38 28 19 19 18 9 7 7 3 9 5 (2) 2 (5) 0 0 0 0 0 0

2/1/2024 5,000 9,990 2,157 884 548 332 240 165 129 91 63 52 41 31 21 20 19 10 8 8 3 10 6 (3) 2 (5) 0 0 0 0 0 0

Hi! Unfortunately, there is no solution how to get the sum of conditionally formatted cells using a custom function. Use a macro.

Is there a way of modifying the CountCellsByColor function to treat merged cells as just one?

This doesnt work when counting colour on conditionally formatted cells - it only works on cells which have been coloured manually. How do you fix this?

Hi! Pay attention to the following paragraph of the article above: Count and sum conditionally formatted cells. It covers your case completely

The Count and Sum routines are excellent and work very well for me.

How about a MaxCellByColor routine?

In one (1) of 3 tabs, I track my bicycling Time (Col A) and then Date (Col B), year by year.

The condition that exists is I use my bike outdoors and also backed up to a trainer for indoor riding - This data is Cell Colored different from outdoor rides.

I need to be able to breakout the MaxByCellColor for the subset of data for the trainer rides to isolate the Longest Time single time (MAX) spent on the Trainer.

All other stats I can pull using the Count and Sum routines.

I tried a few things, but I am a very early beginner in coding... ;-)

Any suggestions - Thanks!

Hi! We do not do VBA code creation or customization on request.

Hi, I tried to paste the custom function “CountCellsByColor” in my view code area of my worksheet and it’s coming back with “#Name?” as the answer in the cell I want to count all my green cells. Can anyone assist?

Hi! Without seeing your data, it is impossible to understand the cause of the error. I think these articles should help: Why custom functions are not available and #NAME error in Excel: reasons and fixes

Hi! How do I count only individual characters of specific font (colour) in a the range of cells?

For example, I have a range of cells containing simple text strings: [B V A] [B I M] [M K B] . The first "B" is black (auto) coloured, the second and third "B"s are red coloured.

How do I count separately black B's and red B's (to get the result: black B - 1, red B - 2) in the range?

Thanks!

Hi! For your task, you need to write a new custom function.