Count cells that contain odd numbers
This tutorial shows how to count cells that contain odd numbers through the use of an Excel formula or VBA
Example: Count cells that contain odd numbers
|
=SUMPRODUCT(--(MOD(B5:B9,2)=1))
|
This formula uses the Excel MOD function to return the remainder of 1 for odd numbers and 0 for even numbers. The Excel SUMPRODUCT function then performs this calculation for every cell in the specified range (B5:B9) and returns the total number of occurrences if the function returned a value of 1, which in this examples includes three odd values (15, 27 and 5).
|
Sub Count_cells_that_contain_odd_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count cells that contain odd numbers
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(B5:B9,2)=1))"
ws.Range("D5").Formula = "=SUMPRODUCT(--(MOD(B5:B9,2)=1))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Data Range: Select the values that you want to test and count by changing the range ("B5:B9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the data from which you want to count the number of cells that contain odd numbers 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.
Data Range: Select the values that you want to test and count by changing the range ("B5:B9") in the VBA code.
Worksheet Selection: Select the worksheet which captures the data from which you want to count the number of cells that contain odd numbers 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 cells that contain odd numbers from a range using VBA
VBA
Sub Count_cells_that_contain_odd_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count cells that contain odd numbers by looping through rows 5 to 9 in column B
For x = 5 To 9
For x = 5 To 9
cellmod = ws.Range("B" & x) Mod 2
oddnum = oddnum + cellmod
Next x
ws.Range("D5") = oddnum
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 the number of cells that contain odd numbers by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 9. Therefore, the range that this code will loop through will be ("B5:B9"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of cells that contain odd numbers 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 the number of cells that contain odd numbers by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 9. Therefore, the range that this code will loop through will be ("B5:B9"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures a range of cells from which you want to count the number of cells that contain odd numbers 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 cells that contain odd numbers from a range using VBA
VBA
Sub Count_cells_that_contain_odd_numbers()
'declare variables
Dim ws As Worksheet
Dim DataRng As Range
Dim ws As Worksheet
Dim DataRng As Range
Set ws = Worksheets("Analysis")
Set DataRng = ws.Range("B5:B9")
Set DataRng = ws.Range("B5:B9")
'count cells that contain odd numbers by looping through each cell in range (B5:B9)
For Each xcell In DataRng
For Each xcell In DataRng
cellmod = xcell Mod 2
oddnum = oddnum + cellmod
Next xcell
ws.Range("D5") = oddnum
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 the number of cells that contain odd numbers by changing the range reference ("B5:B9") in the VBA code. In this example the range is assigned to a variable name (DataRng) which you can also change 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 odd numbers 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 the number of cells that contain odd numbers by changing the range reference ("B5:B9") in the VBA code. In this example the range is assigned to a variable name (DataRng) which you can also change 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 odd numbers 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 cells that contain only odd numbers, by using an Excel formula or VBA.
This tutorial provides one Excel method, through the use of the SUMPRODUCT and MOD functions, which are used to count the number of cells in a selected range that contain only odd numbers. In this example, the formula counts the cells that contain only odd numbers in range (B5:B9).
There are three VBA methods that are provided in this tutorial. The first method uses the VBA formula function and the exact same formula that is provided in the Excel method. The second and third methods both use the Mod function and the For Loop to loop through each cell in the specified range to count the number of cells that contain only odd numbers. The difference between these methods is that the second method quantifies the column and row references separately and then loops through each of the rows that have been defined in the code. The third method defines the entire range and then loops through each cell in that range.
FORMULA
=SUMPRODUCT(--(MOD(range,2)=1))
=SUMPRODUCT(--(MOD(range,2)=1))
ARGUMENTS
range: A range of values that you want to test and count for odd numbers.
range: A range of values that you want to test and count for odd numbers.
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 cells that contain even numbers | How to count cells that contain even numbers we need to use a combination of Excel SUMPRODUCT and MOD functions |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| MOD Function | The Excel MOD function returns the remainder after dividing a number by the divisor |