Count numeric cells in a range
This tutorial shows how to count cells that contain numeric values in a specific range through the use of Excel formulas or VBA
Example: Count numeric cells in a range
METHOD 1. Count numeric cells in a range using the COUNT function
EXCEL
|
=COUNT(B5:C11)
|
The formula uses the Excel COUNT function to return the number of cells that contain numeric values in a specified range. In this example the formula counts the number of numeric cells from range (B5:C11). Please note that if a cell contains both text and numeric values or is a blank cell the formula will not count such cells.
|
METHOD 2. Count numeric cells in a range using the SUMPRODUCT and ISNUMBER functions
EXCEL
|
=SUMPRODUCT(--ISNUMBER(B5:C11))
|
The formula uses a combination of the Excel SUMPRODUCT and ISNUMBER functions to return the number of cells that contain numeric values in a specified range. The ISNUMBER function returns a TRUE value if a cell is considered numeric and the SUMPRODUCT function sums all of the TRUE values. In this example the formula counts the number of numeric cells from range (B5:C11). Similar to the first method, if a cell contains both text and numeric values or is a blank cell the formula will not count such cells.
|
METHOD 1. Count numeric cells in a range using COUNT function
VBA
Sub Count_numeric_cells_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count numeric cells in range (B5:C11) using the COUNT function
ws.Range("F4") = Application.WorksheetFunction.Count(ws.Range("B5:C11"))
ws.Range("F4") = Application.WorksheetFunction.Count(ws.Range("B5:C11"))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Range: Select the range from which you want to count the number of numeric cells by changing the range reference ("B5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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 ("F4") in the VBA code.
Range: Select the range from which you want to count the number of numeric cells by changing the range reference ("B5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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.
METHOD 2. Count numeric cells in a range using a Formula function with SUMPRODUCT and ISNUMBER functions
VBA
Sub Count_numeric_cells_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count numeric cells in range (B5:C11) using the SUMPRODUCT and ISNUMBER functions
ws.Range("F4").Formula = "=SUMPRODUCT(--ISNUMBER(B5:C11))"
ws.Range("F4").Formula = "=SUMPRODUCT(--ISNUMBER(B5:C11))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Range: Select the range from which you want to count the number of numeric cells by changing the range reference ("B5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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 ("F4") in the VBA code.
Range: Select the range from which you want to count the number of numeric cells by changing the range reference ("B5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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.
METHOD 3. Count numeric cells in a range using a Formula function with A COUNT function
VBA
Sub Count_numeric_cells_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count numeric cells in range (B5:C11) using the COUNT function
ws.Range("F4").Formula = "=COUNT(B5:C11)"
ws.Range("F4").Formula = "=COUNT(B5:C11)"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Range: Select the range from which you want to count the number of numeric cells by changing the range reference ("B5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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 ("F4") in the VBA code.
Range: Select the range from which you want to count the number of numeric cells by changing the range reference ("B5:C11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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.
METHOD 4. Count numeric cells in a range with assigned objects
VBA
Sub Count_numeric_cells_in_a_range()
'declare variables
Dim ws As Worksheet
Dim Output As Range
Dim Data As Range
Dim CountFormula As Double
Dim ws As Worksheet
Dim Output As Range
Dim Data As Range
Dim CountFormula As Double
Set ws = Worksheets("Analysis")
Set Output = ws.Range("F4")
Set Data = ws.Range("B5:C11")
CountFormula = Application.WorksheetFunction.Count(Data)
Set Output = ws.Range("F4")
Set Data = ws.Range("B5:C11")
CountFormula = Application.WorksheetFunction.Count(Data)
'count numeric cells in the Data range using the COUNT function
Output = CountFormula
Output = CountFormula
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code. The output range has been assigned against a range variable that has been to to 'Output'. 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 the number of numeric cells by changing the range reference ("B5:C11") in the VBA code. You can also change the name of this object variable, by changing the name 'Data' in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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.
Count Formula: Select the name of the CountFormula object variable, by changing the name 'CountFormula' in the VBA code.
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code. The output range has been assigned against a range variable that has been to to 'Output'. 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 the number of numeric cells by changing the range reference ("B5:C11") in the VBA code. You can also change the name of this object variable, by changing the name 'Data' in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of numeric cells 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.
Count Formula: Select the name of the CountFormula object variable, by changing the name 'CountFormula' in the VBA code.
Explanation about the formulas used to count numeric cells in a range
EXPLANATION
EXPLANATION
This tutorial shows how to count cells that contain numeric values in a specific range, using Excel formulas and VBA.
This tutorial shows how to count cells that contain numeric values in a specific range, using Excel formulas and VBA.
The two Excel formulas used in this tutorial to count numeric cells in a specified range use three Excel functions (COUNT, SUMPRODUCT and ISNUMBER functions).
The first formula in this tutorial uses the Excel COUNT function. Given that the nature of the Excel COUNT function is to return the number of cells that contain only numeric values in a specified range, it can simply be used on its own to count numeric cells in a specified range.
The second formula in this tutorial uses a combination of the Excel SUMPRODUCT and ISNUMBER functions. The ISNUMBER function tests each cell in a selected range to identify if it's a numeric cell. The function will return a TRUE value for all numeric cells and a FALSE value for all non-numeric cells. The SUMPRODUCT function will then sum all of the TRUE values returned by the ISNUMBER function. Therefore, this formula will return the total number of numeric cells in a specified range.
This tutorial shows four VBA methods that can be applied to return a total number of numeric cells from a specified range.
The first VBA method uses the COUNT function to return the total number of numeric cells from a range. The fourth VBA method is identical to the first, however, in the fourth method all of the object variables are assigned with an object.
The second and third VBA methods use the Formula property (in A1-style) with the same formulas that is used in the Excel methods.
If a cell contains both text and numeric values or it's blank, this cell will not be counted as a numeric cell and will not be counted in any of the formulas applied in this tutorial.
FORMULA (using COUNT function)
=COUNT(range)
=COUNT(range)
FORMULA (using SUMPRODUCT and ISNUMBER functions)
=SUMPRODUCT(--ISNUMBER(range))
=SUMPRODUCT(--ISNUMBER(range))
ARGUMENTS
range: The range from which you want to count the number of numeric cells.
range: The range from which you want to count the number of numeric cells.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count numeric characters in a range | How to count the total number of numeric characters in a range using Excel and VBA methods | |
| Count number of cells in a range | How to count the total number of cells in a range using Excel and VBA methods | |
| Count numeric characters in a cell | How to count the total number of numeric characters in a cell using Excel and VBA methods | |
| Count number of specific characters in a range | How to count the total number of specific characters in a range using Excel and VBA methods | |
| Count duplicate values | How to count duplicate values in a range using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| COUNT Function | The Excel COUNT function returns the number of cells that contain numeric values in a specified range | |
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| ISNUMBER Function | The Excel ISNUMBER function tests a specified value (cell) if it's a numeric value and returns TRUE if it's a text value or FALSE if it's not a numeric value |