Count cells that contain text
This tutorial shows how to count cells that contain text through the use of an Excel formula or VBA
Example: Count cells that contain text
|
=COUNTIF(B5:B9,"*")
|
This formula uses the Excel COUNTIF function combined with an asterisk (*), as the criteria, to count the number of cells that contain text value in range (B5:B9).
|
Sub Count_cells_that_contain_text()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to count cells that contain text
ws.Range("D5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B9"), "*")
ws.Range("D5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B9"), "*")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from which you want to count cells that contain text by changing the range reference ("C8:C14") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range from which you want to count cells that contain text by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from which you want to count cells that contain text by changing the range reference ("C8:C14") in the VBA code.
Worksheet Selection: Select the worksheet which captures the range from which you want to count cells that contain text by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Sub Count_cells_that_contain_text()
'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim output As Range
Dim ws As Worksheet
Dim rng As Range
Dim output As Range
Set ws = Worksheets("Analysis")
Set rng = ws.Range("B5:B9")
Set output = ws.Range("D5")
Set rng = ws.Range("B5:B9")
Set output = ws.Range("D5")
'apply the formula to count cells that contain text
output = Application.WorksheetFunction.CountIf(rng, "*")
output = Application.WorksheetFunction.CountIf(rng, "*")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code. You can also change the name of this object variable, by changing the name 'output' in the VBA code.
Range: Select the range from which you want to count cells that contain text by changing the range reference ("C8:C14") in the VBA code. You can also change the name of this object variable, by changing the name 'rng' in the VBA code.
Worksheet Selection: Select the worksheet which captures the range from which you want to count cells that contain text by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code. You can also change the name of this object variable, by changing the name 'output' in the VBA code.
Range: Select the range from which you want to count cells that contain text by changing the range reference ("C8:C14") in the VBA code. You can also change the name of this object variable, by changing the name 'rng' in the VBA code.
Worksheet Selection: Select the worksheet which captures the range from which you want to count cells that contain text by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial explains how to count the number of cells, from a specified range, that contain text using Excel and VBA methods.
The Excel method uses the Excel COUNTIF function, combined with asterisk (*) as the criteria to count the number of cells that contain text in a specified range.
Both of the VBA methods make use of the CountIf Worksheet Function to count the number of cells that contain text in a specified range. The difference between the two methods is that the second method assigns objects to all the relevant object variables.
FORMULA
=COUNTIF(range, "*")
=COUNTIF(range, "*")
ARGUMENTS
range: The range of cells you want to count from.
"*" criteria: Using "*" as the criteria will count all of the cells in the selected range that are populated with text.
range: The range of cells you want to count from.
"*" criteria: Using "*" as the criteria will count all of the cells in the selected range that are populated with text.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a cell contains text | How to test if a cell contains text and return a specified value you using Excel and VBA methods | |
| Count cells that contain a specific value | How to count cells that contain a specific value using Excel and VBA methods | |
| Count cells that do not contain a specific value | How to count cells that do not contain a specific value using Excel and VBA methods | |
| Count cells that are blank | How to count cells that are blank using Excel and VBA methods | |
| Count cells that are not blank | How to count cells that are not blank using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| COUNTIF Function | The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria |