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

829 comments

  1. Thank You!
    Question - I am trying to nest CountCellsByColor.
    =CountCellsByColor(B2:Q33, B4) and it produced 37 (the number I was looking for).
    Now I am trying to count the colored cells if they are blank.
    =COUNTIFS(B2:Q33, "", B2:Q33, CountCellsByColor(B2:Q33, B4)) and it keeps returning zero which is not correct. What am I doing wrong?

    • Hi! The COUNTIFS function cannot use other functions and formulas as criteria. To count empty cells that are highlighted in a specific color, use the SUMPRODUCT function.
      To get the code for the desired color, use the custom function GetCellColor() as described in the article above. Identify empty cells using the ISBLANK function.
      The formula might look like this:

      =SUMPRODUCT((GetCellColor(A1:A20)=GetCellColor(H1)) * ISBLANK(A1:A20))

  2. Hi Alexander,

    How to count cells by color in Excel;
    This works perfectly and has saved many tiring works.

    The counts are correct but when I change the colors of the cells, the formula doesnt pick this up, unless I go back to cell and press "Enter" again.
    Am I missing something?

    • Hi! Only when you change values in the worksheet does Excel automatically recalculate formulas. The result is not changed by changing the cell format. Therefore, changing colors does not automatically recalculate.

  3. Hi. I love Ablebits! But, I have a problem. When trying to use Count and Sum by color, the Insert result as formula checkbox is not available to me. It works and I can paste the amount in my spreadsheet but not the formula. I am assuming there is some setup or option I have missed, any suggestions?

  4. Hi There,

    How to count conditionally formatted cell with RED Color?

    Count how many RED Color cells...

    • Hi! Carefully read the paragraph in the article above: Count and sum conditionally formatted cells. Ensure that you understand the information presented.

  5. CountCellsByColor works fine using the Excel application (on Windows 10) but when shared through a link and viewed from a browser I get the #NAME?.
    After removing the line containing "Application.Volatile" it also works fine from a browser view.

      • Thanks! understood. Nevertheless, I'm sharing in viewing mode only so no changes can be made and all Excel Online results look correct after removing the Application.Volatile line. Looks like the Excel Desktop calculated results have been saved and are shown in Excel Online without needing to run VBA(?)

        • Hi! Application.Volatile causes Excel to recalculate the values of the custom function every time there is a change in the worksheet. Since it doesn't work in Excel Online, it causes an immediate error.

  6. Is there a way to use your "How to count and sum conditionally formatted cells using VBA macro" script listed above in a cell as a result, instead of a pop-up box as you have shown in your sample? I need to be able to total several columns that are conditionally formatted (and regularly updated with new data) with a yellow highlight.

    • Hi! To count the sum of cells highlighted in a particular color, you can use the custom GetCellColor function. For example:

      =SUM((GetCellColor(A1:A20)=65535)*A1:A20)

      This function is described in detail in the article above.

  7. Hi, the count cells by color funtions work great! But is it possible to combine these and count cells with specific fill and font colours, e.g. yellow cells with red font?

    • Hi! To count yellow cells with red font, try using a formula like this:

      =SUM((GetCellColor(A1:A10)=65535)*(GetFontColor(A1:A10)=255))

      • Thanks! That formula only seems to return a 1 or a 0. I've been able to achieve the desired result by using GetCellColor and GetFontColor in two new colums, then using COUNTIFS to count the required cominations. I'm thinking there must be a better way though.

  8. Hi :)

    Is it possible to make this function continue to work once the data is sorted? Everything worked perfectly until I tried to sort my data, then it changed my coloured cell to #REF (see example. =CountCellsByColor(H6:ON6,#REF!) ) and now I just get #VALUE!. I made sure I picked a cell that would never move positions but still the formula couldn't handle the sort.

    • Hi! I was unable to reproduce your problem when sorting the data. This error usually occurs not when sorting, but when deleting rows referenced in the formula.

  9. Hey, the code countscellsbycolor works fine but if I change a cell color the value of my code doesn't change. I have to clic on the code for the value to change.
    It is easier if the value change instantly so i don't have to clic on the code each time.

  10. Did exactly as instructions suggest, doesn't seem to work.
    Used this formula: =CountCellsByColor(I61:I66,I55)
    Had I55 cell in color
    Returned response is "#NAME?"
    Using Mac Excel
    Thank you!

      • Hi, this seems to be working for me (thank you!) however I need it to calculate two separate columns that are not directly next to each other. For instance, E8:E50 and G8:50. When entering =CountCellsByColor(E8:E50,G8:G50,A52) it returns #VALUE! When I do =CountCellsByColor(E8:E50:G8:G50,A52) it counts the cells in column F but that's the only way the formula will accept it. What am I doing wrong?

        • Hi! The formula returns a #VALUE! error because you have written the formula incorrectly. Read the manual above carefully. You cannot use two data ranges for counting.

          • Thank you. As a novice, I guess my question is, is there at all a way to count more than two columns (that are not directly next to one another)? Whether in the formula or within the code of the UDF? Thank you.

  11. Hi, I have a task to count a quantity of red numbers among numbers which in black in excel cell. Is there any formula which can help me.
    For instance in a cell I have 4,5,7,9,3 and 6. Numbers 4 and 6 are in red, whereas others are in black as usual. So in the cell I have 2 quantity of red numbers. How can I use excel formula (if present) in order to save time.

    • Hi! All the necessary information is in the the following paragraph of the article above: Sum values by font color.
      If all your numbers are written in one cell as text, it is not possible to count the numbers by color.

  12. Hi there,

    How to count the conditionally formatted cell with color RED?

    All the above method seems not working

    Pls guide...

    Thanks

    • Hi! I can't write the formulas for you in your workbook. Follow the detailed instructions in the article above. You can use the sample file linked at the end of the article.

  13. I am using the SumCellsByColour whic is fantatic - thank you!

    Could anyone help advise how I could also add an additional if condition. Basically I want to add a coloured cell as long as another cell in my table also has specific text. e.g. sum the coloured cell as long as cell C2 = "include". Grateful for any advice or guidance that anyone can offer.

    • Should have said here where I put C2, this would of course be a range in a table corresponding to the range where I'm looking at colour. So SumCellsByColour(table_col_1,colour) as long as table_col_2 = "specific text". Hope this is clearer.

    • Hello! Use the GetCellColor function to get the color of the cell. I believe the following formula will help you solve your task:

      =SUM((GetCellColor(D2:D20)=GetCellColor($K$1))*(J2:J20="text"))

      K1 - cell with color sample.

  14. I tried using your Subcountbyconditionalformat, but I do not get the correct count. I created a calendar view that highlights Saturday & Sunday as gray based on the date in the cell. I also have a conditional format that states if the date is not within a certain month to leave the cell white. The counter seems to count any cell that has a conditional format that could make it gray instead of the end result of multiple conditional formats. Is this how it should operate?

    • Hi! The function counts only cells with a certain color. Check your data and formulas. It is possible that the color is incorrectly specified in the formula.

    • Honestly, this tool is amazing, not even chat GPT suggested a precise process as the one stated here.

      Thank you

  15. Hi all,

    I just added this sum function "=SumCellsByColor" in my workbook earlier and save it. it worked perfectly but when I open the same excel again after few days, how come it is not automatically calculating. I have no idea have to resolve the issue. Any help please on how I fix the problem? Thanks!

  16. The macro for counting the conditionally formatted cell works great - but the popup is NOT what i need - i want the count to post to a Cell. is that possible without having to do it manually myself? in other words - if 6x in a my row/range - the "condition is met" (green) - i want 6 to appear in a summary table cell below. Then i want to do that again for a different range/row (different team member) and so on. Thoughts?

      • Hello! This is very helpful, thank you! I also have a question about editing the macro code. I am not a coder, can you please explain where (and how) in the conditional formatting macro code we would need to edit to output the count into a cell and not a popup box? Thank you!

  17. IT is working perfectly ...Thanku for this

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

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

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

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

  19. Hi, great work, thank you.
    I am having some issues, I have 29 cells, of which 8 are green fill, through conditional formatting condition ">0".
    I have another cell, manually filled with green, as sample colour, as specified in tutorial. When I run the macro, it counts "29" with colour "000000".
    Some help please'?

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

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

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

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

        Thanks!

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

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

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

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