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

• By using User Defined Function
• By using a powerful feature – Kutools for Excel
• By using Filter and SUBTOTAL

Count and sum cells based on font color

• By using User Defined Function
• By using an easy feature – Kutools for Excel

Count and sum cells based on conditional formatting color

• By using VBA code
• By using a smart feature – Kutools for Excel

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:

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:

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:

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:

Related Articles: