Count number of occurrences with multiple criteria
This tutorial shows how to count the number of occurrences in range that meeting multiple criteria through the use of an Excel formula, with the COUNTIFS function, or VBA
Example: Count number of occurrences with multiple criteria
|
=COUNTIFS(B9:B15,C5,C9:C15,">"&C6)
|
This formula counts the number of occurrences where range (B9:B15) captures the word "Bread" and in the corresponding cell in range (C9:C15) it captures a value greater than 400. In this example there are two such occurrences in row 9 and row 12, where cells B9 and B12 capture the word "Bread" and the corresponding cells C9 and C12 capture values greater than 400.
|
Sub Count_number_of_occurrences_with_multiple_criteria()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the number of occurrences with two criteria
ws.Range("F8") = Application.WorksheetFunction.CountIfs(ws.Range("B9:B15"), ws.Range("C5"), ws.Range("C9:C15"), ">" & ws.Range("C6"))
ws.Range("F8") = Application.WorksheetFunction.CountIfs(ws.Range("B9:B15"), ws.Range("C5"), ws.Range("C9:C15"), ">" & ws.Range("C6"))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Data Range: Select the range that you want to count from by changing the ranges ("B9:B15") and ("C9:C15") to any ranges in the worksheet that doesn't conflict with the formula.
Criteria Values: Select the specific value that you want to test for by changing the value in cell ("C5") and ("C6").
Worksheet Selection: Select the worksheet in which you want to count the number of occurrences with multiple criteria 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. In this example the criteria that are applied are captured in the same worksheet.
Output Range: Select the output range by changing the cell reference ("F8") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Data Range: Select the range that you want to count from by changing the ranges ("B9:B15") and ("C9:C15") to any ranges in the worksheet that doesn't conflict with the formula.
Criteria Values: Select the specific value that you want to test for by changing the value in cell ("C5") and ("C6").
Worksheet Selection: Select the worksheet in which you want to count the number of occurrences with multiple criteria 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. In this example the criteria that are applied are captured in the same worksheet.
EXPLANATION
This tutorial shows how to count the number of times multiple criteria occurs in a range through the use of an Excel formula, with the COUNTIFS function, or VBA. In this example we are only applying two criteria against two ranges.
This tutorial shows how to count the number of times multiple criteria occurs in a range through the use of an Excel formula, with the COUNTIFS function, or VBA. In this example we are only applying two criteria against two ranges.
FORMULA
=COUNTIFS(criteria_range1, criteria1, [criteria_rangle2], [criteria2], ...)
=COUNTIFS(criteria_range1, criteria1, [criteria_rangle2], [criteria2], ...)
ARGUMENTS
criteria_range1:: The range of cells that you want to apply the criteria1 against.
criteria1: The criteria to be tested against criteria_range1.
criteria_range2:: The range of cells that you want to apply the criteria2 against.
criteria2: The criteria to be tested against criteria_range2.
criteria_range1:: The range of cells that you want to apply the criteria1 against.
criteria1: The criteria to be tested against criteria_range1.
criteria_range2:: The range of cells that you want to apply the criteria2 against.
criteria2: The criteria to be tested against criteria_range2.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count cells that equal to a specific value | How to count cells that are equal to a specific value using Excel and VBA | |
| Count most frequently occurring text | How to count the most frequently occurring text using Excel and VBA | |
| Count number of specific characters in a range | How to count the total number of specific characters in a range using Excel and VBA | |
| Count cells if greater than | How to count cells that are greater than a specific value using Excel and VBA | |
| Count cells if less than | How to count cells that are less than a specific value using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| COUNTIFS Function | The Excel COUNTIFS function returns the number of cells in a range that meet one or more specified criteria |