*I this article you will learn how to count cells by color in Excel and get the sum of colored cells. These solutions work both for cells colored manually and with conditional formatting. You will also learn how to filter cells by several colors in Excel 2010, Excel 2013 and Excel 2016.*

If you actively use diverse fill and font colors in your Excel worksheets to differentiate between various types of cells or values, you may want to know how many cells are highlighted in a certain color. If your cells' values are numbers, you may also want to automatically calculate the sum of cells shaded with the same color, e.g. the sum of all red cells.

As all of us know, Microsoft Excel provides a variety of formulas for different purposes, and it would be logical to assume that there are some to count cells by color. But regrettably, there is no formula that would let us sum by color or count by color in a usual Excel worksheet.

Apart from using third-party add-ins, there is only one possible solution - utilize User Defined Functions. If you know very little about this technology or have never heard this term before, don't be afraid, you will not have to write the code yourself. You will find the perfect code (written by our Excel guru) here and all that you will have to do is copy / paste it into your workbook.

## How to count by color and sum by color in an Excel worksheet

Suppose you have a table listing your company's orders where the cells in the Delivery column are colored based on their value - "*Due in X Days*" cells are orange, "*Delivered*" items are green and "*Past Due*" orders are red.

What we want now is automatically count cells by color, i.e. calculate the number of red, green and orange cells in the worksheet. As I explained above, there is no straightforward solution to this task. But luckily we have very skilled and knowledgeable Excel gurus in our team and one of them has written the faultless code for Excel 2010, 2013 and 2016. So, move on with the 5 quick steps below and you will know the number and sum of your color cells in a few minutes.

- Open your Excel workbook and press Alt+F11 to open Visual Basic Editor (VBE).
- Right-click on your workbook name under "
*Project-VBAProject*" in the right hand part of the screen, and then choose**Insert > Module**from the context menu.

- Add the following code to your worksheet:
Function GetCellColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If If xlRange.Count > 1 Then ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count) For indRow = 1 To xlRange.Rows.Count For indColumn = 1 To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color Next Next GetCellColor = arResults Else GetCellColor = xlRange.Interior.Color End If End Function Function GetCellFontColor(xlRange As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If xlRange Is Nothing Then Set xlRange = Application.ThisCell End If If xlRange.Count > 1 Then ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count) For indRow = 1 To xlRange.Rows.Count For indColumn = 1 To xlRange.Columns.Count arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color Next Next GetCellFontColor = arResults Else GetCellFontColor = xlRange.Font.Color End If End Function Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cellRefColor.Cells(1, 1).Interior.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Interior.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByColor = cntRes End Function Function SumCellsByColor(rData As Range, cellRefColor As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cellRefColor.Cells(1, 1).Interior.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByColor = sumRes End Function Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cellRefColor.Cells(1, 1).Font.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByFontColor = cntRes End Function Function SumCellsByFontColor(rData As Range, cellRefColor As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cellRefColor.Cells(1, 1).Font.Color For Each cellCurrent In rData If indRefColor = cellCurrent.Font.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByFontColor = sumRes End Function

- Save your workbook as "
*Excel 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.

- Now that all "behind the scenes" work is done for you by the just added user-defined function, choose the cell where you want to output the results and enter the
**CountCellsByColor**function into it:

CountCellsByColor(*range*,*color code*)In this example, we use the formula

`=CountCellsByColor(F2:F14,A17)`

where*F2:F14*is the range containing color-coded cells you want to count and*A17*is the cell with a certain background color, a red one in our case.In a similar way, you write the formula for the other colors you want to count, yellow and green in our table.

If you have numerical data in colored cells (e.g. the

**Qty.**column in our table), you can add up the values based on a certain color by using an analogous SumCellsByColor function:SumCellsByColor(*range*,*color code*)As demonstrated in the screenshot above, we used the formula

`=SumCellsByColor(D2:D14,A17)`

where*D2:D14*is the range and*A17*is the cell with a color pattern.In a similar way you can count cells and sum cells' values by

**font color**using the`CountCellsByFontColor`

and`SumCellsByFontColor`

functions, respectively.**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. Please don't be angry with us, this is not a bug of the code : )

In fact, it is the normal behavior of all Excel macros, VBA scripts and User-Defined Functions. The point is that all such functions are called with a change of a worksheet's data only and Excel does not perceive changing the font color or cell color as a data change. So, after coloring cells manually, 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.

## Sum by color and count by color across the entire workbook

The VB script below was written in response to Connor's comment (also by our Excel's guru Alex) and does exactly what Connor requested, namely counts and sums the cells of a certain color in all worksheets of the workbook. So, here comes the code:

Function WbkCountCellsByColor(cellRefColor As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkCountCellsByColor = vWbkRes End Function Function WbkSumCellsByColor(cellRefColor As Range) Dim vWbkRes Dim wshCurrent As Worksheet Application.ScreenUpdating = False Application.Calculation = xlCalculationManual vWbkRes = 0 For Each wshCurrent In Worksheets wshCurrent.Activate vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkSumCellsByColor = vWbkRes End Function

You use this macro in the same manner as the previous code and output the count and sum of the colored cells with the help of the following formulas, `=WbkCountCellsByColor()`

and `=WbkSumCellsByColor()`

, respectively. Simply enter either formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color in brackets, e.g. *=WbkSumCellsByColor(A1)*, and the formula will display the sum of all the cells shaded with the same color in your workbook.

### Custom functions to get a cell's background color, font color and color code

Here you will find a summary of all the functions we've used in this example as well as a couple of new ones that retrieve color codes.

**Note:**Please remember that all of these formulas will work only if you have added the user-defined function to your Excel workbook as demonstrated earlier in the article.

##### Functions to count by color:

`CountCellsByColor(range, color code)`

- counts cells with the specified background color.In the above example, we used the following formula to count cells by color

*=CountCellsByColor(F2:F14,A17)*where*F2:F14*is the selected range and*A17*is the cell with the needed background color. You can use all other formulas listed below in a similar way.`CountCellsByFontColor(range, color code)`

- counts cells with the specified font color.

##### Formulas to sum by color:

`SumCellsByColor(range, color code)`

- calculates the sum of cells with a certain background color.`SumCellsByFontColor(range, color code)`

- calculates the sum of cells with a certain font color.

##### Formulas to get the color code:

`GetCellFontColor(cell)`

- returns the color code of the font color of a specified cell.`GetCellColor(cell)`

- returns the color code of the background color of a specified cell.

Well, counting cells based on color and getting the sum of colored cells was pretty easy, wasn't it? Of course if you have that little VBA gem that makes the magic happen : ) But what if you do not color cells manually and rather use conditional formatting, as we discussed in these two articles How to change the background color of cells and How to change a row's color based on cell value?

## How to count by color and sum cells colored using conditional formatting

If you have applied conditional formatting to color cells based on their values and now you want to count cells by color or sum the values in colored cells, I have bad news - there is no universal user-defined function that would sum by color or count color cells and output the resulting numbers directly in the specified cells. At least, I am not aware of any such function, alas : (

Of course, you can find tons of VBA code on the Internet that attempts to do this, but all those codes (at least the examples I've come across, **do not process** conditional formatting such as "*Format all cells based on their values*", "*Format only top or bottom ranked values*", "*Format only values that are above or below average*", "*Format only unique or duplicate values*". Besides that nearly all those VBA codes have a number of specificities and limitations because of which they may not work correctly with certain workbooks or data types. All in all, you can try your luck and google for an ideal solution and if you happen to find one, please do come back and post your finding here!

The VBA code below overcomes the above mentioned limitations and works in Microsoft Excel 2010, Excel 2013 and Excel 2016 spreadsheets **with all types of condition formatting** (kudos to Alex again!). As a result, it displays the number of colored cells and the sum of values in those cells, no matter which type of conditional formats are used in a sheet.

Sub SumCountByConditionalFormat() Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Dim sumRes Dim cntCells As Long Dim indCurCell As Long cntRes = 0 sumRes = 0 cntCells = Selection.CountLarge indRefColor = ActiveCell.DisplayFormat.Interior.Color For indCurCell = 1 To (cntCells - 1) If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then cntRes = cntRes + 1 sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes) End If Next MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _ "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _ Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color" End Sub

### How to use the code to count colored cells and sum their values

- Add the above code to your worksheet as explained in the first example.
- Select a range or ranges where you want to count colored cells or/and sum by color if you have numerical data.
- Press and hold Ctrl, select one cell with the needed color, and then release the Ctrl key.
- Press Alt+F8 to open the list of macros in your workbook.
- Select the
**SumCountByConditionalFormat**macro and click**Run**.

As a result, you will see the following message:

For this example, we selected the Qty. column and got the following numbers:

**Count**is the number of the cells with a particular color, a reddish color in our case that marks "*Past Due*" cells.**Sum**is the sum of values of all red cells in the Qty. column, i.e. the total number of "*Past Due*" items.**Color**is the Hexadecimal color code of a selected cell, D2 in our case.

### Sample workbook for download

If you have any difficulties with adding the scripts to your Excel workbooks, such as compilation errors, formulas not working and so on, please download this sample workbook with the *CountCellsByColor* and *SumCellsByColor* functions ready for use and try them on your data.

## Fastest way to count and sum cells by color in Excel - new!

*Updated on 1-Jul-2014.*

When we published this article, we hoped it would be popular because we used to get a lot of questions about how to count and sum cells by color in Excel. But even in our wildest expectations, we did not think it was going to be that popular! What you see in comments on this page is just a tiny portion of the enormous feedback we have received. So, our team decided to take a step further and create an Excel add-in that would count and sum cells **by the color you specify** or by **all colors in the selected range**.

Let me introduce you our brand new add-in - Count & Sum by Color for Excel 2016, 2013, Excel 2010, 2007 and 2003. Once installed, it will place 2 buttons onto the *Ablebits Data* tab on your Excel ribbon - *One Color* and *All Colors*, as you can see in the screenshot below:

### Count and sum cells by the selected color

You simply click the **One Color** button on the ribbon and have the *Count & Sum by Color* pane open at the left of the worksheet. On the pane, you select:

- The range where you want to count and sum the cells
- Any color-coded cell
- Color option - either background or font color

Once done, click **Calculate** and see the result in the lower part of the pane straight away! Apart from the count and sum, the add-in calculates the average and finds the max and min values. No macros, no formulas, no pain :)

### Count and sum cells by all colors in the selected range

The **All Colors** option works basically in the same way, except that you do not have to choose the color. In the "*Show results for*" section, you can select any of the following options: *Count*, *Sum*, *Average*, *Maximum* or *Minimum* value.

If you want to copy the results to your worksheet, click the **Paste results...** button at the bottom of the *Count & Sum by Color* pane.

And here is a list of the **main features** you will find in the *Count & Sum by Color* add-in:

- Count and sum cells by color in all versions of Excel 2016, 2013, 2010, 2007 and 2003.
- Handle cells color-coded manually and with conditional formatting.
- Besides counting and calculating the sum, the following functions are available: Average, Maximum and Mininum values.
- An option to recalculate colored cells automatically when a new range is selected.
- Copy and paste the results to any location on the current spreadsheet or to any other sheet.

You can download a trial version of the *Count & Sum by Color* add-in here. Hopefully you will like it, but we are keen to know your feedback, anyway : )

### How can I get the Count & Sum by Color add-in?

Currently the add-in is available as part of the Ultimate Suite for Excel. This is a collection of our best tools especially designed to deal with the most tedious, painstaking and error-prone tasks in Excel.

In addition to the **Count & Sum by Color** add-in, the Ultimate Suite includes about 40 other tools that can help you to:

- Merge data from different tables
- Remove duplicates
- Combine duplicate rows into one
- Merge cells, rows and columns
- Find and replace in all workbooks
- Clean data in worksheets
- And much more!

If you like the tools, be sure to benefit from a special 15% off coupon code that we provide especially for our blog readers: **AB14-BlogSpo**

Hopefully, in this article you have found the information you were looking for. If not, you are welcome to post a comment and we will try to help!

Hi,

Just wanted to say thanks so much for sharing this awesome code.

Excel is already an amazing tool and people like you just make it better.

Thanks,

Brett

Thanks for the code .. it works great !!

Great Stuff. Thanks for sharing.

Thanks for sharing, I have certain merged cells to count in between the range. is there a solution yet for that

I see that the code works for everybody but me, so it must be me, but would still like know why it doesn't work. The error on the worksheet in cell F2 is #Name?. I first hand copied from the web site the code, then hand typed it in, still the same error. Then in cell F2 , I hand typed the formula, that was said to type. =CountCellsByColor(A1:A10,E2), where E2 is the cell that has the color I'am trying to count. I work on Excel 2010, and Windows 7. Looking for how many yellow cells there are.

Steve Martin, you are not the only one who is having the problem. I am glad you figured it out. Could you please share what was wrong. I am working in Microsoft Excel for Mac v15.28. I entered pasted the code into the vbe, then added the formula as was specified, and I also received the #NAME? reply in the box with the formula. I have no idea what I did wrong. any help would be greatly appreciated.

If you are saving the macro in your personal workbook, the name of the function will be PERSONAL.XLSB!CountCellsByColor, so you will get a #NAME error if you just put CountCellsByColor (if you save it just to the workbook, this name should work just fine)

Please ignore my previous post I finally figured out my blunders.

Hi,

Am running Macro with cells that have both hyperlinks to server as well as conditional formatting of them cells. Have copied the codes from above script (CountCellsByColor in addition to the Conditional Formatting code later in the page) and they fail to capture the cells which are conditionally formatted, returning a result of '0'.

Moving the data selection area outwith the hyperlink/formatted area returns valid sum data just cant seem to get the Conditionally Formatted cells to be recognised. Ground to a halt here, help...

You guys are awesome !! It works :)

This macro for "How to count by color and sum cells colored using conditional formatting" is a big help. Well done. I was wondering if there is a way to take the output that pops up in the Message Box and have that info put into a cell. For instance, the Count is what I am really looking for. Is there a way to have the Count displayed in a cell for the range selected instead of a popup window?

Hi Please help me with how i can use indirect formula in excel 2010 because when i m trying enter formula i am getting following error #REF!

I am entering formula indirect(select cells) but i am not getting when i am entering formula like this =indirect("Select") i am getting the outcome but not able to use for multiple cell and sheets need your help to get the indirect formula in excel 2010

This is a fantastic tip !

Thankyou so much :)

hi,

how to count the date between one week or two weeks.

for example in cell A1 is date. I want to count the date after one week( between 2 to 7weeks).what is the formula I can apply. I got the formula to count for today's date or overdue date countifs(a1:a10,"<=&today(),b1:b10,d15), but I cannot get the formula to count the date in between one week.

1/8/16

2/8/16

3/8/16

8/8/16

11/8/16

12/8/16

15/8/16

17/8/16

I followed the instructions verbatim for counting cells that contain a color and it didn't work. When I try to use the formula, Excel returns #NAME?

I did save the spreadsheet as a macro-enabled spreadsheet. I even went back into the VBA editor to make sure the code was still there and it was. (and I had copied the code verbatim by selecting it from this website and pasting it.)

Hi,

Your example is good one.

I am in the same problem, but what about merged cell. How do I count merged cell fill with specific color?

regardsd,

Santosh

Hi,

I'm using the color count for conditional formatting Excel 2010. How can you have the count be placed in a cell automatically after entering data instead of having to run the rule and only getting the result in a pop-up box?

Im using Excel 2013. What is the formula to count the cell color of specific information within the cell. EXAMPLE: I have APPLES(red), GRAPES(green) and STRAWBERRIES (red). I want to return the count of cells colored red that are STRAWBERRIES only, not all cells colored red.

Hi Carl, I'm needing the exact same info! I'd like to count by cell color, IF a specific text criteria is met (just like you said, Cell is RED and contains text "STRAWBERRIES"). I do a lot with data, and automating things like this would be a huge help. I've tried combining formulas and although they don't error out, I'm not achieving the response I need. Does anyone know if there is a solution?

I've seen the question I have a few times but there is never an answer associated. I run reports for my boss and some of them require totaling by sum and count for programs during the month based on conditional formatting. The formal works in that I can see the count and sum but, a) I need to include the numbers in my report and b) the count and sums are in separate boxes. Is there a way to populate the answer into a cell with the needed count or sum based on conditional formatting rather than a pop up box which doesn't work for my boss when I print the report.

Thank you, worked great

i can not able to see color and sum by color in an Excel worksheet.so i need help from you.please explain it brief

Hi,

Great stuff.. have learnt a lot for your website.

Need one help regarding sum of cells based on color formatting. The code provided is macro, how to convert this to a function so that I can use it as and when needed

Thanks,

Prasad

Thanks for the great article! I am working on a duel scoring system where each has a numerical score (1-5) and a traffic light score (by filling in the cell colour with red, Amber or green) the two scores are not related. Is it possible to modify the code so that it calculates the average score and fills in the cell colour according to the agreement of the traffic light score (all green = green, all red = red, anything else = Amber)?

To count cells by colour on the same worksheet, Microsoft recommends a macro based on just 10 lines of code, compared with you umpteen lines. Is there a special reason to your code?

I just noticed that the MS code just does a count, whereas your code sums as well. Apologies for my misunderstanding.

I cannot get the workbook sum function to work

I tried your VBA code for counting conditionally formatted cells. I need this code to be a worksheet function. I have over 80 columns of data to apply the code to. It is not practical to select range and reference cell, the press Alt+F8 80 times.

Count for colors works super slow but perfectly! Thank you so much.

Thanks mate :) this is very useful and helpful

I have set up an excel sheet tracking the performance of kids in sport. The columns in question return a result of the test in question. Based on the result, it will be colored, green, yellow, brown or red.

I copied the text you recommended and put it into the Macros window as instructed. I then tried counting how many cells returning numbers with the different colors mentioned above, using the "CountCellsByFontColor" but I couldn't get it to work, regardless whether the text was bold or otherwise.

can you help me with this

Thank you for a great post. I was able to implement for a colleague in 5 mins (2007). Use of "key" or reference cell is great - no need to find the color code. Well done!

One simple update that might be useful in the original code is to skip over hidden cells, like those hidden by a filter. Here's what I did to CountCellsByColor:

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long

Dim indRefColor As Long

Dim cellCurrent As Range

Dim cntRes As Long

Application.Volatile

cntRes = 0

indRefColor = cellRefColor.Cells(1, 1).Interior.Color

For Each cellCurrent In rData

If indRefColor = cellCurrent.Interior.Color And cellCurrent.Rows.Hidden = False Then cntRes = cntRes + 1

Next cellCurrent

CountCellsByColor = cntRes

End Function

Thank you! its really a bbbiiiiiigggg help! Works fine with me :)

Hi, can you tell me how to use the CountCellsByColor formula if the range isn't adjacent cells. For example, I want to count cells if a certain colour where the range is A1, D1, G1 rather than A1:G1?

Thanks.

I got this to work perfectly on another spreadsheet. Now I'm trying to use it again and I'm getting an error.

It says "Compile error: Sub or Function not defined."

I'd appreciate any help.

Thanks,

Mike

Sorry, I'm using the WbkSumCellsByColor function.

I have the same problem. See my post 236 below. I am beginning to think the code does not work with the latest version of Excel 2016.

Hi, I am doing a project. I have excel file with huge data. I want to add datas between two randomly placed data between two rows with same color or same name. I would appreciate any help!!!!

Thanks!!!

hi,

i trying to use =CountCellsByFontColor(V3:V21;Q3)

but this code dont work well if it count by horizontal

Hi...

I'm using a multiple data and to make it simple i'd prefer to use macro that can easily count the color, wherein if color green it gives 40 points but if it less than the desired target count of 5, it gives 0 point. I'm trying to use the CountCellsByFontColor and i got an answer, yet my data is complicated due to some conditions like sample below.

Sample:

Target: 99.30% yield

Color Code if On-Target: Green Below Target: Red

Target Attendance Performance at least: >=5

Condition, yes you hit the target yield based on overall output and you got color green, but you're only 4 in attendance, so, instead of giving 40 points, you will receive 0 point.

Thank you in advance.

I carefully followed the instructions in this post, but I cannot get the code to compile using Windows 10 Pro 64bit and Excel 2016. When I paste it into the VBA editor, many of the lines appear in red. When I try to run the code, I get a compile error that I cannot always clear.

I can clear the lines that begin with Dim by removing the leading spaces, but others are more difficult or not possible for me to clear with what I know about VBA.

Is this code still valid for the latest version of Excel 2016? Or, what am I doing wrong?

Hey, James,

the code is working perfectly, and it should work for you as well. You're probably missing some lines. Try using the VBA codes from these examples.

Hi

I love your code. Thanks for making it available.I am using a filtered list and have added the suggested modification from reply 32 so it only counts filtered lists (which is great and is working)

It works but not quite for what I am trying to achieve.

Its a school and its tracking grades for males and females. In effect I have one column where the autofilter can be M (for boys), F (for females), or both (for all students). I then have a second column where the colour coded grade is (it can be red, amber, green)

What I want to do is to be able to filter by Boys for example, then count in the grade list all red grades, amber and green grades for boys. Then do the same for girls. Then just leave boys and girls ticked in the autofilter

I guess what I am trying to do is to count all the red colours in my filtered list by girls and then in the same filtered list all the red ones by boys, then same for amber then green and display all in the same place the totals for each

I have tried combining countcellsbycolor with IF statements, countifs, sumproduct and so on. But I cant get it to work right.

What seems to happen mostly is that will boys and girls ticked, I am getting the count of all red boxes or boys and girls but not breaking them down into how many boys and how many girls. Same for amber, same for green.

Please can you tell me if there is a way to combine countifs, if, sumproduct etc to count the red amber green by gender for the various combinations of just boys, just girls or both in the autofilter

Can happily send spreadsheet

Thanks

Hello,

I am new to excel/vb/macros and I have an issue that I can't resolve. I've tried to manipulate your code but I'm messing it up without desired output.

I want to check predefined range of cell for cells that have conditional formatting for background color (i.e. green) and list and highlight all green cells that don't have any value in them. I've found a code that is supposed to do almost exactly what I want but it doesn't work in my case - no results displayed. Your comments would be appreciated.

Regards,

Adam

Sub Red_id()

Dim ws As Worksheet

Set ws = Sheets("Sheet1")

Dim i As Integer

i = 1

Do Until i = 11

If ws.Range("C" & i).Interior.Color = RGB(255, 0, 0) Then

Debug.Print "C" & i & " is red!!"

End If

i = i + 1

Loop

End Sub

Hi Friend,

I just want to know, hot to color the cells by using a single cell value.

ie. - Cell one value - 20

Want to fill specific color in next 20 cells....

Hi,

Thank you so much for the code. It worked perfectly for me until I need the sum of cells in a certain font color while the background colors of these cells are different. It seems to be only adding the cells with the same background color as the cell that defines the font color, and leaving out those cells with a different background color. Anyone know how to solve this?

Thanks in advance!!

how to add cells having same background color across different workbooks

I would like to thank you for this wonderful tutorial. This has helped me tremendously. I currently use your code to count how many passes (green) and fails (red) i have on a particular column on a specific worksheet in my workbook. Once the data is populated, I use a pivot to auto update a chart for reference. I would really like to use conditional formatting on that column, so a if a user selected pass, it would automatically turn green, but when I do this, the code stops registering color. I've tried using the count by color and sum cells colored using conditional formatting but I think i'm doing something wrong. I can't get it to work. Can you assist?

Quick question... Just wondering why this code was made so difficult and mucky... for example :

Function CountColors(Check As Range, Base As Range) As Long

For Each i In Check

If Base.Cells(1, 1).Interior.Color = Check.Interior.Color Then: Amount = Amount + 1

Next i

CountColors = Amount

End Function

Works exactly the same way...

Had that backwards ;)

Function CountColors(Check As Range, Base As Range) As Long

For Each i In Check

If Check.Cells(1, 1).Interior.Color = Base.Interior.Color Then: Amount = Amount + 1

Next i

CountColors = Amount

End Function

Hi,

The VBA worked wonderfully. That's a smart coding.

Thanks.

Hello Team,

I am looking solution of my problem. is there any vba which change subject date and body date automatic in outlook13. i used to send daily sales report on daily basis so i am looking any code which automatic change date to today()-1.

Hello.

Thank you for contacting us.

We are always ready to help you, but we do not cover the programming area (VBA-related questions).

You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

Sorry we can't assist you better.

Just having a bit of trouble running the SumCellsByFontColor Macro. I'm hoping someone can give me a tip here; I'm new to Visual Basic.

Whenever I run the Macro, I get a "Compile Error, Syntax Error" with the "Dim indRefColor As Long" highlighted in Blue. (I can send a screenshot if that works better.)

In all cases, I have entered only cut'n'paste data from that shown above - nothing entered manually.

What am I doing wrong. I could easily believe I'm leaving out a step or two.

Thanks.

Thank you so so much! It works perfectly!

Hi there,

I have been trying to use the code and I think the VB part is ok. However, when I try to type in the function I get an error as I enter the criteria section. for example =countbycellcolor($c$33:$c$66,A5) excel gives me an error when I click on the A5 and says this format is not correct. I use excel 2013

Very pleased with this material on how to count numbers of cells with certain colours, and that's all I want to report here. I nearly always would enter a code, like 1 or 2 to a cell to record its category and use countif, but a particular file I am working on needs to be more visual and has a macro button or two to 'click' certain colours into cells, so when I needed to count them up I was stuck. But not now! Thank you.

FORMULA UPDATING? PLEASE TRY & SEND AN ANSWER TO THIS ONE :-):

I emailed 7 hours ago that the AbleBits method shown for counting colors was the very solution to the problem I was having. I have just noticed something, even so, and I therefore have a question:

I seem to need to go into edit mode (F2) with some cell or other, after a change to the colour of a cell has just occurred, in order for the formula to update. Otherwise I may have just created, say, 2 cells with a particular font instead of 1, and the count has been saying 1. I find that the count number (1) does not update immediately to 2 - not until I edit and enter some cell or other. That brings about some updating across the spreadsheet which includes this UDF. Whereas an 'ordinary' formula will update as soon as the data, on which it is based, has been altered. How can I get around that? Hope you can help me here, thanks.

LOTUSMAN

Excelent Tool I was loocking for that since many days ago and now I got it thanks for post that friend

Great routines!!!

BTW, given that some of them helped me, I generated a new function called "SumProductCellsByColor(Range 1 column, cell with color pattern, colum 2)". So, if someone want to take advantage of this code please send me an email I will share it.

Hi there, working on Excel 2016. Whenever I copy and paste formula after inserting module entered. The message appears "the formula type contains an error". The cells all have wording in which is why I utilised the first module above.

Same for me in excel 2016, "the formula type contains an error". The debugger highlights: Function SumCellsByColor(rData As Range, cellRefColor As Range).

Hello,

if you still need assistance with your task, could you please send us your workbook to support@ablebits.com? Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.

Don't forget to link this comment in your message.

Our technical specialist will take a look at your task and try to help.

Hi,

thanks for the guide. It does however have limitations which is a shame. In my case, I have an excel spreadsheet with conditional formatting, so if something is a year out of date for instance, the cell fill colour will go to red. However, whilst the colour is displayed, excel actually lists the no fill for the cell colour within the font ribbon.

Hi,

Ive now seen the conditional format section on the above article. It can be used to count numbers by running the script, but does not generate any summary within excell that can be used. Is there a way of doing this?

Thanks

Probably doing something wrong. However, when trying any of the functions receive #VALUE! error (when stepping through, appears to occur immediately). Any suggestions? Thanks

Please disregard previous question (#256), when copy/paste from latest file worked fine (for some reason could not from instruction page). Thanks for solution - much appreciated.

Thanks and really appreciate your efforts. Liked your code for the Count of cells through Conditional formatting. Is it possible to display the results in excel cells also?

Thank you for this code. I have used it to count cells by their conditional formatted colour. What I would like to do is push this a step further and be able to define the range of cells to count and also the colour to look for before having the result entered into a cell for further calculating to be done with it. Any idea how to do this?

Thanks for the guide. It does however have limitations which is a shame. In my case, I have an excel spreadsheet with conditional formatting, so if something is a year out of date for instance, the cell fill colour will go to red. However, whilst the colour is displayed, excel actually lists the no fill for the cell colour within the font ribbon.

thank u

I have a problem with the formula SumCellByFontColor.

It doesn't sum the correct number. Meaning the correct amount is 5122,45 and it sums up 5121,00. Cell range is right. Decimals are correct. Am I suppose to do something else to correct this?

Thanks in advance.

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

This code is great. With the count on conditional formatting why when running the macro the results are not show in the spreadsheet? Is that possible?

thanks

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

This code worked fine, but it made inputting data on my spreadsheet incredibly slow, everytime I type something Excel freezes and it takes at least 5 seconds so I can type again. And I don't even have that many lines: 600 at the moment.

Am I doing something wrong or is this code that poorly optimised?

I love this formula, but I am struggling with running an "If" Formula with it so I can use 3 basic colors instead of 9 (or potentially more).

I love this formula, but I am struggling getting it to work in an IF function.

If Column I = 8 (or G, or L) and Column B is highlighted yellow (or red or green) how many cells are highlighted yellow?

Here is my current formula for I=8, B=yellow

=if('RawData'!$I$1:$I$500=8,(countcellsbycolor('RawData'!$B$1:$B$500,Counts!G3)),"")

Thank you!

Hello, Margaret,

Unfortunately, your task can be solved only by changing the work of the CountCellsByColor function in your VBA macro.

Since we do not cover the programming area (VBA-related questions), we can't help you with this. I can advise you to look for the solution in VBA sections on mrexcel.com or excelforum.com.

Sorry I can't assist you better.

Thank you, thank you, thank you! This worked out perfectly!!

I just want to say thank you VERY much for this easy to use and detailed tutorial! Top quality! I am bookmarking your site for future use! Great job!!

Hi, But what if I'd like to count if & color? Can I combine Countifs and CountCellsByColor?

Example order date 20-Okt-13 (column B) & Red (Column F)?

you are fabulous! Thank you so much!

the code is not working.

my cell shows #NAME?

its an error. can u rectify it?

Hi,

this is amazing tool, but I have a little problem. If I want to use a conditional formatting, the code doesn't work. The font is changing to red via conditional formatting, but the code sees it like black. After I change color of font handly to red, everything is OK.

Can you help me please? Thank you very much.

This worked like a charm (once I got my fixed my slightly mismatched colors). I will pay this good deed forward when the situation presents itself!

Thank you!

Question - How would I add in that with the color it also needs to find a certain name in the sheet before it adds the total together?

Hi Everyone same issue as 72 above if using conditional formatting it results in a 0 return where as if i manually fill the cell to the desired colour it then returns a 1.

if there a way of counting the cells bases on the conditional formatting of those cells

I have the functions like CountCellsByColor and same for fonts, thank you very much, They help a lot.

I am using these for my job tracking sheet, How did the job get created? What is the current status?

Is there a function that lets me combine the 2 features?

Something like

If Count, CellRange, Cellcolor=Green and fontcolor=Red then Add 1.

In what I am doing, I will never add the numbers together, they are all job numbers, all I need is just the the count of true to both checks.

Thanks

Thanks for Sharing, this is awesome

Good Morning each time I infill a cell with the color selected it does not continue to sum the total unless I click on the formulated cell and hot return. Am I missing something?

hi,

if you look just above the section on 'Sum by color and count by color across the entire workbook' then there is a warning about this (it's just how excel works so nothing wrong with the code)

Hi,

I am Polash. I Can not run this code. I try step by step. Last 3 days I try to very much but not success. Please solved this. What can i do.? I need this formula for my office work......

Without VB code can not run this.....?

I'm having an issue that I can’t work out that combines applying Conditional Formatting to number cells (the amount paid) based on the date in the cell to the left of them (the date the payment was made). This date needs to be between the dates stated in other cells i.e. 'check that date is greater than date in cell ai10 but less than that in aj10. If it meets that criteria then I want it to colour the amount paid cell the colour I have selected in the Conditional Formatting. Can anyone help?

I have used the above VBA and formula in this article which looks like it is working so that once I have the above sorted and the cells are automatically coloured I can then get a total amount paid between the 2 stated dates.

Thank You

Simon

Many Thanks. I tried a half dozen different solutions from as many websites and this is the only one that worked flawlessly.

Awesome, thanks very detailed and works perfectly, not often when I try one of the solutions i searched for does it work first time!!!!!! This does THANKS

Thank You so much for putting this out there! Was just what I was looking for and more!

Is it possible to count a coloured cell by contents.

Example:

B2 contains the text 'E1' with no fill and cells B3 and B4 contains 'E1' with a blue fill. B5 is a blank a blue cell.

In B6 I would expect the answer to be 2 as I only want to count the blue fill cells with E1....

bless you!

The code did well for simply counting cells by color, but my needs are more complicated. I'd like to learn how you set up the table color coding in the Delivery Column. I'm trying to use a date entered to set a Due Date and color code it yellow after 15 days past due and red after 30, THEN count cells by color.

James:

Can you provide some samples?

Also, what is the relationship between the Due Date and the date entered?

Decommission Ticket Dt Past Due Date(=IF(ISBLANK(V3)=FALSE,(V3+45))

FALSE

05/01/2018 06/15/2018

Generated due date should be 45 days after the ticket date.

When the due date turns 15 days past due, it should turn yellow (Cell Value > =$Z$26+15), 30 turns red. False remains white. I'm using Conditional Formatting for this.

So far, generating the dates is the easy part and I can singally affect a past due date with color but it all jacks up when I bring it down the column.+

Another thing I struggle with is the syntax of:

=INDEX(C3:C520,MATCH(0,COUNTIF(C3:C520),AA323))

I want a count of unique names in column C that matches the criteria in AA323.

I'm trying to show # of DBs in resolved status when DB Name and Status are in separate columns.

Dear Ablebits

Thanks for your work! When I run use the countcellbycolor function it returns an error:

Compile error: Syntax error

The debugger points to this line:

Dim indRefColor As Long

I'm using the code in Excel 2016 on Windows 10. A number of other people have the same problem. Would you be able to resolve it?

Hi,

The VBA works brilliantly when I manually colour the cells, but it is not work with cells that have been condtionally formatted. Any suggestions?

Can you help

=countcellsbycolor(range,criteria)

it is not working

i want to count colored cells and font please help me in this matter

thanks

good job! but when using other kind of conditional formatting such as "top 10 item" or "color scale" or "icon set", the macro doesn't work. do you have a solution for these kind of conditional formatting?

thank you

Hai Dear,

I can see, everybody is using this stuff in very friendly and good, unfortunately its is not working with me, may am not much expert in Excel. Please any one can help me to find the solution.

I filled with 4 different colours (RED, BLACK, YELLOW & GREEN) from A1 to F55, my requirement is need to find how many Red, Black,Yellow & Green.

Someone can help me for this??

i tried this on worksheet where cell contains numerical values. =CountCellsByColor(F2:F14,A17) formula is not taking count ,pls help