Count cells with values in odd columns
This tutorial shows how to count cells with values in odd columns through the use of an Excel formula or VBA
Example: Count cells with values in odd columns
|
=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=1),--(C5:I5<>""))
|
This formula uses the Excel COLUMN and MOD functions to return the remainder of 1 for a cell in an odd columns and 0 for a cell in an even column. The Excel SUMPRODUCT function then performs this calculation for every cell in the specified range (C5:I5) that contains a value and returns the number of occurrences, which includes three odd values (15, 12 and 18 from cells C5, G5 and I5, respectively), and sums these occurrences. Therefore, the formula will return a value of three given there are three cells in odd columns, within the selected range, that contain values.
|
Sub Count_cells_with_values_in_odd_columns()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count cells with values in odd columns
ws.Range("K5").Formula = "=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=1),--(C5:I5<>""))"
ws.Range("K5").Formula = "=SUMPRODUCT(--(MOD(COLUMN(C5:I5),2)=1),--(C5:I5<>""))"
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:B11")in the VBA code.
Worksheet Selection: Select the worksheet in which you want to count the cells with values in odd columns 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:B11")in the VBA code.
Worksheet Selection: Select the worksheet in which you want to count the cells with values in odd columns 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 with values in odd columns only from a selected range through the use of an Excel formula or VBA.
This tutorial provides one Excel method that can be applied to count the number of cells with values in odd columns by using the Excel SUMPRODUCT, MOD and COLUMN functions.
This tutorial provides one VBA method that can be applied to count the number of cells with values in odd columns from a selected range using the VBA formula and the exact same formula that is provided in the Excel method.
FORMULA
=SUMPRODUCT(--(MOD(COLUMN(range),2)=1),--(range<>""))
=SUMPRODUCT(--(MOD(COLUMN(range),2)=1),--(range<>""))
ARGUMENTS
range: A range of values that you want to test if there are cells in odd columns that contain values and count the number of these occurrences.
range: A range of values that you want to test if there are cells in odd columns that contain values and count the number of these occurrences.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count cells with values in even rows | How to count cells with values in even rows using Excel and VBA methods | |
| Count cells with values in odd rows | How to count cells with values in odd rows using Excel and VBA methods | |
| Count cells with values in even columns | How to count cells with values in even columns using Excel and VBA methods | |
| Count cells with specific value in odd rows | How to count cells with specific value in odd rows using Excel and VBA methods | |
| Count cells with specific value in even rows | How to count cells with specific value in even rows using Excel and VBA methods |
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 | |
| COLUMN Function | The Excel COLUMN function returns the first column number of the selected reference |