Count cells that do not equal to a specific value
To count cells that do not equal to a specific value you can apply an Excel or a VBA method using the Excel COUNTIF function
Example: Count cells that do not equal to a specific value
|
=COUNTIF(C8:C14,"<>"&C5)
|
The formula uses the Excel COUNTIF function to count the number of cells in a range (C8:C14) that have a value that is not equal to the value in cell C5.
|
Sub Count_cells_that_do_not_equal_to_a_specific_value()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to calculate if a range does not contain the value in cell (C5) and then return a specified value
ws.Range("E8") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), "<>" & ws.Range("C5"))
ws.Range("E8") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), "<>" & ws.Range("C5"))
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Data Range: Ensure that the data that you want to count from is captured in range ("C8:C14") in the Analysis worksheet.
Specific Value: Input the specific value that you want to test for in cell ("C5") in the Analysis worksheet.
Worksheet Name: Have a worksheet named Analysis.
Data Range: Ensure that the data that you want to count from is captured in range ("C8:C14") in the Analysis worksheet.
Specific Value: Input the specific value that you want to test for in cell ("C5") in the Analysis worksheet.
ADJUSTABLE PARAMETERS
Specific Value: Select the specific value that you want to test for by changing the value in cell ("C5"). Alternatively, you can replace ws.Range("C5") in the VBA code with the specific value or a defined name that represents the specific value that you want to test for.
Date Range: Select the range that you want to count from by changing the range ("C8:C14") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("E8") to any cell in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To count cells that do not contain a specific value you can apply an Excel or a VBA method. The formula used to count cells that do not contain a specific value is driven by an Excel COUNTIF function.
In both the VBA and Excel examples the formula counts the number of cells from the specified range (C8:C14) that does not contain equal to loss, which is the value in cell C5. This is achieved through the use of the Excel COUNTIF function.
To count cells that do not contain a specific value you can apply an Excel or a VBA method. The formula used to count cells that do not contain a specific value is driven by an Excel COUNTIF function.
In both the VBA and Excel examples the formula counts the number of cells from the specified range (C8:C14) that does not contain equal to loss, which is the value in cell C5. This is achieved through the use of the Excel COUNTIF function.
FORMULA
=COUNTIF(range,"<>"&value)
=COUNTIF(range,"<>"&value)
ARGUMENTS
range: The range of cells you want to count from.
value: The value that is used to determine which of the cells should be counted, from a specified range, if the cells' value is not equal to this value.
range: The range of cells you want to count from.
value: The value that is used to determine which of the cells should be counted, from a specified range, if the cells' value is not equal to this value.