Skip to main content

Excel Tips: Count/sum cells by color (background, font, conditional formatting)

Author: Xiaoyang Last Modified: 2023-11-03

In daily tasks, color marking is a popular method for quickly distinguishing and highlighting crucial data. But, how do we count or sum cell data based on specific color (fill color, font color, conditional formatting)? By default, Excel doesn't offer a direct feature to count or sum by color. Nevertheless, with some tricks and indirect methods, we can still achieve this. This article will explore how to count or sum data by color.

Count and sum cells based on background color

Count and sum cells based on font color

Count and sum cells based on conditional formatting color


Video: Count and sum cells based on color


Count and sum cells based on background color

For example, if you have a range of data where the values are filled with different background colors as shown in the screenshot below. To count or sum the cells based on a specific color, Excel doesn't offer a direct feature to count or sum cells based on their background color. However, with a bit of ingenuity and some handy techniques, you can accomplish this task. Let's explore some useful methods in this section.


Count and sum cells by background color with User Defined Function

Here, we will show you how to create and use such a User Defined Function to solve this task in Excel. Please do with the following steps:

Step 1: Open the VBA module editor and copy the code

  1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
  2. In the opened window, click Insert > Module to create a new blank module.
  3. Then, copy and paste the below code into the blank module.
    VBA code: Count and sum cells based on background color
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Step 2: Create formulas to count and sum cells by background color

After pasting the above code, close the module window, then apply the following formulas:

  • Count cells based on the specific background color:
    Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note: In this formula, G2 is the reference cell with the specific background color you want to match; $B$2:$E$12 is the range where you want to count the number of cells of G2's color; FALSE is used to count cells with matching color.
  • Sum cells based on the specific background color:
    Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note: In this formula, G2 is the reference cell with the specific background color you want to match; $B$2:$E$12 is the range where you want to count the number of cells of G2's color; TRUE is used to sum cells with matching color.

Count and sum cells by background color with a powerful feature

For those unfamiliar with programming, VBA can appear quite complex. Here, we will introduce a powerful tool - Kutool for Excel, its Count by Color feature allows you to easily calculate (count, sum, average, etc.) based on background color in just a few clicks. Impressively, Count by Color feature goes beyond merely background colors – it can also differentiate and calculate based on font colors and conditional formatting.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific background color. Next, navigate to Kutools Plus and choose Count by Color.

In the Count by Color dialog box, please specify the operations:

  1. Select Standard formatting from the Color method drop down list;
  2. Specify Background from the Count type drop down list, and you can preview the statistical results for each background color in the dialogue box;
  3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now you will get a new workbook with the statistics. See screenshot:

Tips:
  1. The Count by Color feature also supports counting and summing cells based on standard font color, background or font color from conditional formatting, and a combination of both fill and conditional formatting colors.
  2. Interested in this feature, please click to download to get a free trial for 30 days.

Count and sum cells by background color with Filter and SUBTOTAL function

Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column.

Step 1: Apply the SUBTOTAL function

Select blank cells to enter the SUBTOTAL function.

  • To count all cells with the same background color, please enter the formula:
    =SUBTOTAL(102, F2:F16)
  • To sum all cells with the same background color, please enter the formula;
    =SUBTOTAL(109, F2:F16)
  • Note: in the above formulas, 102 represents to count numeric values in a filtered list while excluding hidden cells; 109 represents to sum up values in a filtered list excluding hidden cells; F2:F16 is the range over which either the count or sum will be calculated.

Step 2: Filter cells based on specific color

  1. Select the header of the table, and click Data > Filter. See screenshot:
  2. Click the Filter icon  in the header cell of the Amount column, and click Filter by Color and the specified color you will count by successively. See screenshot:

Result:

After filtering, the SUBTOTAL formulas automatically count and sum the colored cells in the Amount column. See screenshot:

Note: This method requires the colored cells you will count or sum are in the same column.

Count and sum cells based on font color

Want to count or sum cells based on their font color in Excel? Let's say you have the data, like in the given screenshot, with cells containing texts in red, blue, orange and black color. Excel doesn't make this easy by default. But don't worry! In this section, we'll show you some simple tricks to do just that.


Count and sum cells based on font color with User Defined Function

To count and sum cells with specific font colors, the following User Defined Function may help you to solve this task. Please do with the following steps:

Step 1: Open the VBA module editor and copy the code

  1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
  2. In the opened window, click Insert > Module to create a new blank module.
  3. Then, copy and paste the below code into the blank module.
    VBA code: Count and sum cells based on font color
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Step 2: Create formulas to count and sum cells by font color

After pasting the above code, close the module window, then apply the following formulas:

  • Count cells based on the specific font color:
    Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note: In this formula, G2 is the reference cell with the specific font color you want to match; $B$2:$E$12 is the range where you want to count the number of cells of G2's color.
  • Sum cells based on the specific font color:
    Copy or type the formula provided below into your desired cell for the result. Then, drag the fill handle downwards to get other results. See screenshot:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note: In this formula, G2 is the reference cell with the specific font color you want to match; $B$2:$E$12 is the range where you want to count the number of cells of G2's color.

Count and sum cells based on font color with an easy feature

Looking to effortlessly count or sum cell values in Excel based on font color? Dive into Kutools for Excel's Count by Color feature! With this smart tool, counting and summing cells by specific font color becomes a breeze. Discover how Kutools can transform your Excel experience.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific font color. Then, click Kutools Plus > Count by Color to open the Count by Color dialog box.

In the Count by Color dialog box, please specify the operations:

  1. Select Standard formatting from the Color method drop down list;
  2. Specify Font from the Count type drop down list, and you can preview the statistical results for each font color in the dialogue box;
  3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now, you have a new workbook displaying the detailed statistics based on font color. See screenshot:

Tips: Interested in this feature, please click to download to get a free trial for 30 days.

Count and sum cells based on conditional formatting color

In Excel, you may commonly use the Conditional Formatting to apply specific color to cells that meet certain criteria, making data visualization intuitive. But what if you need to count or sum those specially formatted cells? While Excel doesn't offer a direct way for this, here are ways to maneuver around this limitation.


Count and sum conditionally formatted cells with VBA code

Counting and summing conditionally formatted cells in Excel is not straightforward using built-in functions. However, you can accomplish this task using VBA code. Let's go over how you can use VBA for this:

Step 1: Open the VBA module editor and copy the code

  1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
  2. In the opened window, click Insert > Module to create a new blank module.
  3. Then, copy and paste the below code into the blank module.
    VBA code: Count and sum cells based on conditional formatting color
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Step 2: Execute this VBA code

  1. After pasting the code, press F5 key to run this code, a prompt box will appear, please select the data range where you want to count and sum cells based on conditional formatting. Then, click OK, See screenshot:
  2. In another prompt box, select a specific conditional formatting color that you want to count and sum, and click OK button, see screenshot:

Result:

Now, the result, which includes both the count and sum of cells with the specified conditional formatting color, will be displayed in the popped-out box. See screenshot:


Count and sum conditionally formatted cells with a smart feature

If you're looking for other quick and easy methods to count and sum conditionally formatted cells, Kutools for Excel is your go-to solution. Its Count by Color feature can solve this task in just a few clicks. Dive in to discover the efficiency and precision Kutools can bring to your workflow.

After downloading and installing Kutools for Excel, first, select the data range that you want to count or sum cells based on a specific conditional formatting color. Then, click Kutools Plus > Count by Color to open the Count by Color dialog box.

In the Count by Color dialog box, please specify the operations:

  1. Select Conditional formatting from the Color method drop down list;
  2. Specify Background from the Count type drop down list, and you can preview the statistical results for each conditionla formatting color in the dialogue box;
  3. At last, click Generate report to export the calculated results to a new workbook.

Result:

Now, you have a new workbook displaying the detailed statistics based on the conditional formatting color. See screenshot:

Tips: Interested in this feature, please click to download to get a free trial for 30 days.

Related Articles:

  • If the font color is red then return a specific text
  • How could you return a specific text if the font color is red in another cell as below screenshot shown? In this article, I will introduce some tricks for doing some operations based on the red font text in Excel.
  • Filter data by multiple colors
  • Normally, in Excel, you can quickly filter rows with only one color, but, have you ever considered filtering rows with multiple colors at the same time? This article, I will talk about quick trick for you to deal with this problem.
  • Add color to drop down list
  • In Excel, create a drop-down list can help you a lot, and sometimes, you need to color coded the drop down list values depending on the corresponding selected. For instance, I have created a drop-down list of the fruit names, when I select Apple, I need the cell is colored with red automatically, and when I choose Orange, the cell can be colored with orange.
  • Color alternate rows for merged cells
  • It is very helpful to format alternate rows with a different color in a large data for us to scan the data, but, sometimes, there may be some merged cells in your data. To highlight the rows alternately with a different color for the merged cells as below screenshot shown, how could you solve this problem in Excel?
Comments (239)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
HI, i want sum all data with green color, and when I add 1 cell with green color the total will be increase qty, pls help me. Thank you.
This comment was minimized by the moderator on the site
Saya sudah copy VBA, dan pakai petunjuk sesuai di atas, untuk sum font color, tapi hasilnya #NAME. Knpa ya?
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations