How to Count Cells with Specific Text and Fill/Font Color in Excel (4 Ways)

It’s possible that you’ll need to count cells based on specific text or formats, such as fill color and font color. Furthermore, you may be required to meet both conditions. In this tutorial, we will show you some ways to count cells with specific text and fill and font color in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Best Ways to Count Cells with Specific Text and Fill/Font Color in Excel

We’ll go over the four best approaches to count cells with specific text and fill/font color in the sections below. To begin, we’ll use several Excel built-in options, and then we’ll employ VBA programs to complete the process. In the below image, a sample data set is provided which will be used to have the job done.

 Sample Data Set

1. Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

First and foremost, we’ll use the Data tab’s Filter option. Follow the instructions below to do so.

Step 1:

  • Select the cell in the column header.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 2:

  • Go to the Data tab, click on the Filter.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

  • Therefore, a filter icon will appear.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 3:

  • In a blank cell, type the following formula.
=SUBTOTAL(3,B5:B11)

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 4:

  • First to filter by color, click on the icon first.
  • Then, select the Filter by Color.
  • Click on a specific color.
  • Press the Enter.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

  • Therefore, you will get the result as 3, as the 3 cells are formatted with the same fill color.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 5:

  • To count by specific font color, choose the Filter by Font Color.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

  • As a result, you will the value as 3. As 3 of has the same font color.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Read More: Excel Formula to Count Cells with Specific Color (4 Ways)


2. Use the Find & Select Feature to Count Cells with Specific Text and Fill/Font Color in Excel

From the Editing option in Excel, you can count cells by using the Find & Select command.

Follow the outlined steps below.

Step 1:

  • Firstly, click on the Find & select.
  • Choose the Find.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 2:

  • From the Format option, select the Choose Format From cell.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 3:

  • Select any cell you want to count the formats.
  • Then, click on the Find All.
  • Finally, you will obtain result 3 shown below the box.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 4:

  • Now to count cells with specific text font, just click on the cell.
  • Then, choose the Find All option to get the result.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel


3. Run a VBA Code to Count Cells with Specific Text and Fill Color in Excel

Moreover, you can apply VBA codes to do the task. Simply, follow the steps below.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 1:

  • Press Alt  + F11 to open VBA Macro.
  • Click on the Insert.
  • Choose the Module.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 2:

  • Paste the following VBA codes for your table range B5:B12 and lookup value in cell E5.
Sub CountCellsByFontColor()
'Declare variables
    Dim I As Long
    Dim Number As Long
    Dim Rows As Long
    Dim OutputRng As Range
    Dim TableRng, LookupFillColor As Range
    On Error Resume Next
'Set range to variables
    Set TableRng = Range("B5:B12")
    Set LookupFillColor = Range("E5")
'Set a input box for result output
    Set OutputRng = Application.InputBox("select a cell:", "ExcelDemy", Selection.Address, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
'Command to count the rows
    xRows = TableRng.Rows.Count
    Set TableRng = TableRng(1)
'Apply loop
    Number = 0
    For I = 1 To xRows
'Set condition to match the font and count the cells
        If TableRng.Offset(I - 1, 0).Interior.ColorIndex = LookupFillColor.Interior.ColorIndex Then
                Number = Number + 1
        End If
    Next
'command to output the result
    OutputRng = Number
End Sub

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Step 3:

  • Save the program and press F5 to run it.
  • Select any cell where you want to show the result. Here, we select the F5.
  • Finally, press the Enter.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

  • Therefore, the result will show as 4, as the 4 cells from the table have the same fill color format.

Apply Filter and the SUBTOTAL Function to Count Cells with Specific Text and Fill/Font Color in Excel

Notes: To count cells with both the conditions of specific text and fill/font color just add the following extra condition in the VBA code.

If TableRng.Offset(I - 1, 0).Value = LookupFillColor.Value Then
Number = Number + 1
End If

Step 1:

  • So, the paste and run the following final codes.
Sub CountCellsByFillColor()
'Declare variables
    Dim I As Long
    Dim Number As Long
    Dim Rows As Long
    Dim OutputRng As Range
    Dim TableRng, LookupFillColor As Range
    On Error Resume Next
'Set range to variables
    Set TableRng = Range("B5:B12")
    Set LookupFillColor = Range("E5")
'Set a input box for result output
    Set OutputRng = Application.InputBox("select a cell:", "ExcelDemy", Selection.Address, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
'Command to count the rows
    xRows = TableRng.Rows.Count
    Set TableRng = TableRng(1)
'Apply loop
    Number = 0
    For I = 1 To xRows
'Set condition to match the Fillcolor/Interior and count the cells
        If TableRng.Offset(I - 1, 0).Interior.ColorIndex = LookupFillColor.Interior.ColorIndex Then
         If TableRng.Offset(I - 1, 0).Value = LookupFillColor.Value Then
                Number = Number + 1
         End If
        End If
    Next
'command to output the result
    OutputRng = Number
End Sub

Run a VBA Code

Step 2:

  • Run the code to see the result. The result show as 2, as there are 2 same cells with the same text and fill color.

Run a VBA Code


4. Perform a VBA Code to Count Cells with Specific Text and Font Color in Excel

To find and count the specific text with font color follow the steps below.

Run a VBA Code

Step 1:

  • After opening VBA Module, paste the following codes.
Sub CountCellsByFontColor()
'Declare variables
    Dim I As Long
    Dim Number As Long
    Dim Rows As Long
    Dim OutputRng As Range
    Dim TableRng, LookupFillColor As Range
    On Error Resume Next
'Set range to variables
    Set TableRng = Range("B5:B12")
    Set LookupFillColor = Range("E5")
'Set a input box for result output
    Set OutputRng = Application.InputBox("select a cell:", "ExcelDemy", Selection.Address, , , , , 8)
    If OutputRng Is Nothing Then Exit Sub
'Command to count the rows
    xRows = TableRng.Rows.Count
    Set TableRng = TableRng(1)
'Apply loop
    Number = 0
    For I = 1 To xRows
'Set condition to match the font and count the cells
        If TableRng.Offset(I - 1, 0).Font.ColorIndex = LookupFillColor.Font.ColorIndex Then
                Number = Number + 1
        End If
    Next
'command to output the result
    OutputRng = Number
End Sub

Run a VBA Code

Step 2:

  • Then, save the program and press F5 to run.
  • Select a blank cell you want to get the result.
  • Then, click on the OK.

Run a VBA Code

  • As a result, the result will appear as 3.

Run a VBA Code


Conclusion

To summarize, I hope you now know how to count cells that have specific text and a specific fill/font color. Your data should be taught and practiced using all of these ways. Examine the practice book and apply what you’ve learned. We are encouraged to continue giving seminars like this because of your critical support.

Please do not hesitate to contact us if you have any queries. Please share your thoughts in the comments box below.

Your questions will be answered as soon as possible by the Exceldemy staff.

Stay with us and continue to learn.


Further Readings

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

2 Comments
  1. I followed the instructions in this one
    Run a VBA Code to Count Cells with Specific Text and Fill Color in Excel
    but the result is not updating when i edit the range of cells and more cells match the criteria
    i want the result to change when i update the table
    i think this is supposed to be function not sub in the vba code but im not really sure
    i hope you can help me as i need the vba code and the function to run it
    thank you

    • Greetings DANA,
      To update the result after each entry you make, you just need to edit a one-line VBA code. It will update the table range each time you make a new entry in column B.

      The VBA Code:

      Set TableRng = Range(Range("B5"), Range("B5").End(xlDown))

      Now, make new entries and run the program to update the result.
      Hope this will work for you. Please give us feedback if you have any further queries.

Leave a reply

ExcelDemy
Logo