Bhubon Costa
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.
How to Count Cells with Specific Text and Fill/Font Color in Excel: 4 Best Ways
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.
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.
Step 2:
- Go to the Data tab, click on the Filter.
- Therefore, a filter icon will appear.
Step 3:
- In a blank cell, type the following formula.
=SUBTOTAL(3,B5:B11)
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.
- Therefore, you will get the result as 3, as the 3 cells are formatted with the same fill color.
Step 5:
- To count by specific font color, choose the Filter by Font Color.
- As a result, you will the value as 3. As 3 of has the same font color.
Read More: Excel Formula to Count Cells with Specific Color
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.
Step 2:
- From the Format option, select the Choose Format From cell.
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.
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.
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.
Step 1:
- Press Alt + F11 to open VBA Macro.
- Click on the Insert.
- Choose the Module.
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
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.
- Therefore, the result will show as 4, as the 4 cells from the table have the same fill color format.
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 IfStep 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
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.
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.
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
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.
- As a result, the result will appear as 3.
Read More: How to Count Colored Cells in Excel with VBA
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.
Stay with us and continue to learn.
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:
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.
Hi, Is there a way to do this but where the fill of your cell has been previously decided by conditional formatting. I have cells which are conditionally formatted a colour, depending upon the text that is in them.
I then want to count how many of these new cells have the colour in and have specific text.
Hi MR STEVEN J BUNTING,
Greetings. Thanks for commenting. Yes, you can count cells by color with conditional formatting in Excel. To do this, you can follow three procedures: filter feature, table feature, and sort feature. Please follow the following articles if you would like to know more details.
Count Cells by Color with Conditional Formatting in Excel (3 Methods)
Hi, thank you for the suggestions.
I have a question:
Is it possible to have the range covering MULTIPLE COLUMNS to perform a VBA Code to Count Cells with Specific Text and Font Color?
Thank you
Hi JOAO
Thank you for your query.
Yes, you can count cells with specific text and font color in a range covering Multiple Columns using VBA.
I will show you two cases regarding how to count cells by font color of specific text.
Case 1: Count Cells by Font Color of Specific Text
First, let me explain the case a bit. Please have a look at the image.
Here, the font color in the specific text “The Prince of the Skies” (in E5) is Red, which is present in both B and C columns. To count the cells that have the same font color of that specific text, you should run the code given below:
Note:
cell.Font.Color = lookupCell.Font.Color → With this command, Excel is matching the font color of each cell (in Range B5:C12) with the font color of cell E5. If they match, the count variable will increase by 1.
Once you run the code, Excel will show you the result.
Case 2: Count Cells with Specific Text and Font Color
Let’s understand this case first.
Here, the reference text is “Pippo and Clara”. However, the font color is different in B6 and B8.
Now, to count cells by specific text with color, write down the following code:
Once you run the code, you will get an input box.
You have to write the specific text that you desire. Then, on clicking OK, Excel will show the result.
Note:
cell.Value = lookupText And cell.Font.Color = lookupColor.Font.Color → This command is checking two conditions
cell.Value = lookupText: This condition checks if the value of the cell is equal to the value of the lookupText variable.
cell.Font.Color = lookupColor.Font.Color: This condition checks if the font color of the cell is the same as the font color of the lookupColor cell.
If both condition match, the count variable will increase by 1.
Here, the count is 1. Because, for the text (“Pippo and Clara”), the font color matches only once in cell B8.
Thank you. Have a good day!