Count only positive numbers
This tutorial shows how to count cells that contain only positive numbers through the use of an Excel formula or VBA
Example: Count only positive numbers
|
=COUNTIF(B5:B11,">0")
|
This formula uses the Excel COUNTIF function with the greater than 0 criteria (">0") to count only positive numbers from a specified range. In this example the formula will only count cells B5, B7, B8, B10 and B11.
|
Sub Count_only_positive_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count only positive numbers
ws.Range("D5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B11"), ">0")
ws.Range("D5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B11"), ">0")
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 only positive numbers by changing the range reference ("B5:B11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of cells that contain a positive number 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 only positive numbers by changing the range reference ("B5:B11") in the VBA code.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of cells that contain a positive number 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 shows how to count the number of cells that contain only positive numbers in a specified range using an Excel formula and VBA.
Both the Excel and VBA methods make use of the COUNTIF function combined with the greater than 0 (">0") criteria to return the number of cells that contain only positive numbers in a selected range.
FORMULA
=COUNTIF(range, ">0")
=COUNTIF(range, ">0")
ARGUMENTS
range: The range of cells you want to count from and test the criteria against.
">0": The criteria that is used to determine which of the cells, in the specified range, have a positive value and should be counted.
range: The range of cells you want to count from and test the criteria against.
">0": The criteria that is used to determine which of the cells, in the specified range, have a positive value and should be counted.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count cells if less than | How to count cells that are less than a specific value using Excel and VBA methods | |
| Count cells if greater than | How to count cells that are greater than a specific value using Excel and VBA methods | |
| Count cells if less than or equal to | How to count cells that are less than or equal to a specific value using Excel and VBA methods | |
| Count cells if greater than or equal to | How to count cells that are greater than or equal to a specific value using Excel and VBA methods | |
| Count only negative numbers | How to count only negative numbers in a specified range 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 |