How to sum and count cells by color in Excel

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.

Custom functions to count by color in Excel
Function CountCellsByColor(data_range As Range, cell_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByColor = cntRes End Function Function CountCellsByFontColor(data_range As Range, font_color As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long Application.Volatile cntRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then cntRes = cntRes + 1 End If Next cellCurrent CountCellsByFontColor = cntRes End Function

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:

CountCellsByColor(data_range, cell_color)

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:

  1. Insert the code of the CountCellsByColor function in your workbook.
  2. In a cell where you want the result to appear, start typing the formula: =CountCellsByColor(
  3. For the first argument, enter the range in which you want to count colored cells.
  4. For the second argument, supply the cell with the target color.
  5. 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:
Counting cells by color in Excel

Count cells by font color

In case your cell values have different font colors, you can count them using this function:

CountCellsByFontColor(data_range, font_color)

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)
Custom function to count cells by font color in Excel

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.

Custom functions to sum by color in Excel
Function SumCellsByColor(data_range As Range, cell_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = cell_color.Cells(1, 1).Interior.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Interior.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByColor = sumRes End Function Function SumCellsByFontColor(data_range As Range, font_color As Range) Dim indRefColor As Long Dim cellCurrent As Range Dim sumRes Application.Volatile sumRes = 0 indRefColor = font_color.Cells(1, 1).Font.Color For Each cellCurrent In data_range If indRefColor = cellCurrent.Font.Color Then sumRes = WorksheetFunction.Sum(cellCurrent, sumRes) End If Next cellCurrent SumCellsByFontColor = sumRes End Function

Sum values by cell color

To sum by fill color in Excel, this is function to use:

SumCellsByColor(data_range, cell_color)

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 fill color in Excel.

Sum values by font color

To sum numeric values with a specific font color, use this function:

SumCellsByFontColor(data_range, font_color)

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)
Sum values by font color in Excel.

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:

Custom functions to count and sum by color across workbook
Function WbkCountByColor(cell_color 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, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkCountByColor = vWbkRes End Function Function WbkSumByColor(cell_color 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, cell_color) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic WbkSumByColor = vWbkRes End Function

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:

WbkCountByColor(cell_color)

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:

WbkSumByColor(cell_color)

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.

VBA macro to count and sum conditionally formatted cells.
Sub SumCountByConditionalFormat() Dim indRefColor As Long Dim cellsColorSample As Range Dim cntRes As Long Dim sumRes Dim cntCells As Long Dim indCurCell As Long On Error Resume Next cntRes = 0 sumRes = 0 cntCells = Selection.CountLarge Set cellsColorSample = Application.InputBox( _ "Select sample color:", "Select a cell with sample color", _ Application.Selection.Address, Type:=8) If Not (cellsColorSample Is Nothing) Then indRefColor = cellsColorSample.Cells(1, 1).DisplayFormat.Interior.Color For indCurCell = 1 To (cntCells) 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 If End Sub

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:

  1. Select one or more ranges where you want to count and sum colored cells. Make sure the selected range(s) contains numerical data.
  2. Press Alt + F8, select the SumCountByConditionalFormat macro in the list, and click Run.
  3. A small dialog box will pop asking you to select a cell with the sample color. Do this and click OK.

VBA macro to count and sum conditionally formatted cells

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.

A count and sum of conditional formats

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.

Custom functions to get the cell color
Function GetCellColor(cell_ref As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If cell_ref Is Nothing Then Set cell_ref = Application.ThisCell End If If cell_ref.Count > 1 Then ReDim arResults(1 To cell_ref.Rows.Count, 1 To cell_ref.Columns.Count) For indRow = 1 To cell_ref.Rows.Count For indColumn = 1 To cell_ref.Columns.Count arResults(indRow, indColumn) = cell_ref(indRow, indColumn).Interior.Color Next Next GetCellColor = arResults Else GetCellColor = cell_ref.Interior.Color End If End Function Function GetFontColor(cell_ref As Range) Dim indRow, indColumn As Long Dim arResults() Application.Volatile If cell_ref Is Nothing Then Set cell_ref = Application.ThisCell End If If cell_ref.Count > 1 Then ReDim arResults(1 To cell_ref.Rows.Count, 1 To cell_ref.Columns.Count) For indRow = 1 To cell_ref.Rows.Count For indColumn = 1 To cell_ref.Columns.Count arResults(indRow, indColumn) = cell_ref(indRow, indColumn).Font.Color Next Next GetFontColor = arResults Else GetFontColor = cell_ref.Font.Color End If End Function

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:

GetCellColor(cell_ref)

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:

GetFontColor(cell_ref)

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))
Custom functions to get a cell's color in Excel

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:

  1. In your workbook, press Alt + F11 to open Visual Basic Editor.
  2. In the left pane, right-click on the workbook name, and then choose Insert > Module from the context menu.
  3. In the Code window, insert the code of the desired function(s):
  4. 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:
Ablebits Count & Sum by Color tool for Excel

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:
Calculate cells in Excel by selected color.

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.
Calculate all colored cells at once.

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)

Table of contents

788 comments

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

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

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

  4. Thank you so so much! It works perfectly!

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

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

  7. Hi,

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

    Thanks.

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

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

  10. how to add cells having same background color across different workbooks

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

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

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

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

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

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

  17. hi,
    i trying to use =CountCellsByFontColor(V3:V21;Q3)
    but this code dont work well if it count by horizontal

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

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

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

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

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

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

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

  25. Thanks mate :) this is very useful and helpful

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

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

  28. I cannot get the workbook sum function to work

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

  30. 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)?

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

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

  33. Thank you, worked great

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

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

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

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

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

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

  40. This is a fantastic tip !

    Thankyou so much :)

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

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

  43. You guys are awesome !! It works :)

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

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

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

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

  48. Great Stuff. Thanks for sharing.

  49. Thanks for the code .. it works great !!

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)