Count cells that do not contain a specific value
This tutorial shows how to count cells that do not contain a specific value through the use of Excel formulas or VBA
Example: Count cells that do not contain a specific value
|
=COUNTIF(B5:B7,"<>"&"*"&D5&"*")
|
This formula uses the Excel COUNTIF function to count the number of cells in a range (B5:B7) that do not contain a value specified in cell D5.
Given the formula references to a cell that contains a specific value that we are searching for there is a need to apply the double quotation marks around the * sign. |
METHOD 2. Count cells that do not contain a specific value with the value entered directly into the formula
EXCEL
|
=COUNTIF(B5:B7,"<>"&"*Exceldome*")
|
This formula uses the Excel COUNTIF function to count the number of cells in range (B5:B7) that do not contain a value of Exceldome, which is directly entered into the formula. Therefore, if a cell does not contain the specific value in addition to other content, this formula will still recognise and count this cell.
|
Sub Count_cells_that_do_not_contain_a_specific_value()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to count the number of cells in range (B5:B7) that do not contain the value specified in cell (D5)
ws.Range("E5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B7"), "<>" & "*" & ws.Range("D5") & "*")
ws.Range("E5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B7"), "<>" & "*" & ws.Range("D5") & "*")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: Select the range from which you want to count cells that does not contain a specific value by changing the range reference ("B5:B7") in the VBA code.
Specific Value: Select the value that you want to count for if it doesn't exist in a cell by changing the cell reference ("D5") 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 do not contain the specified value 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 ("E5") in the VBA code.
Range: Select the range from which you want to count cells that does not contain a specific value by changing the range reference ("B5:B7") in the VBA code.
Specific Value: Select the value that you want to count for if it doesn't exist in a cell by changing the cell reference ("D5") 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 do not contain the specified value 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 do not contain a specific value using VBA with the value entered directly into the VBA code
VBA
Sub Count_cells_that_do_not_contain_a_specific_value()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to count the number of cells in range (B5:B7) that do not contain Exceldome
ws.Range("E5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B7"), "<>" & "*Exceldome*")
ws.Range("E5") = Application.WorksheetFunction.CountIf(ws.Range("B5:B7"), "<>" & "*Exceldome*")
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Range: Select the range from which you want to count cells that do not contain a specific value by changing the range reference ("B5:B7") in the VBA code.
Specific Value: Select the value that you want to count for if it doesn't exist in a cell by changing the 'Exceldome' value 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 do not contain the specified value 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 ("E5") in the VBA code.
Range: Select the range from which you want to count cells that do not contain a specific value by changing the range reference ("B5:B7") in the VBA code.
Specific Value: Select the value that you want to count for if it doesn't exist in a cell by changing the 'Exceldome' value 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 do not contain the specified value 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 and explains how to count cells that do not contain a specific value using by using Excel formulas or VBA.
This tutorial provides two Excel methods that can be applied to count cells that do not contain a specific value in a selected range by using an Excel COUNTIF function combined with the not equal to sign (<>). The first method sources the value that from a cell, whilst the second method has the value (Exceldome) directly entered into the formula.
This tutorial provides two VBA methods that can be applied to count cells that do not contain a specific value in a selected range. The first method sources the value that from a cell, whilst the second method has the value (Exceldome) directly entered into the VBA code.
By including asterisk (*) in front and behind the value that we are searching for it will ensure that if there is other content in a cell, including the specific value, the formula will not count this cell.
FORMULA (value manually entered)
=COUNTIF(range,"<>"&"*value*")
=COUNTIF(range,"<>"&"*value*")
FORMULA (value sourced from cell reference)
=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 not be counted, from a specified range, if the cells contain 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 not be counted, from a specified range, if the cells contain this value.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a cell contains text | How to test if a cell contains text and return a specified value using Excel and VBA methods | |
| Count cells that contain text | How to count cells that contain text using Excel and VBA methods | |
| Count cells that contain a specific value | How to count cells that contain a specific value using Excel and VBA methods | |
| Count cells that are blank | How to count cells that are blank using Excel and VBA methods | |
| Count cells that are not blank | How to count cells that are not blank 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 |