This Excel tutorial explains how to use the Excel COUNTIFS function with syntax and examples.
criteria1 is used to determine which cells to count. criteria1 is applied against criteria_range1.
criteria_range2, ... criteria_range_n is optional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 127 ranges.
criteria2, ... criteria_n is optional. It is used to determine which cells to count. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.
Based on the spreadsheet above, the following Excel COUNTIFS examples would return:
Description
The Microsoft Excel COUNTIFS function counts the number of cells in a range, that meets a single or multiple criteria.Syntax
The syntax for the Microsoft Excel COUNTIFS function is:COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
Parameters or Arguments
criteria_range1 is the range of cells that you want to apply criteria1 against.criteria1 is used to determine which cells to count. criteria1 is applied against criteria_range1.
criteria_range2, ... criteria_range_n is optional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 127 ranges.
criteria2, ... criteria_n is optional. It is used to determine which cells to count. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.
Applies To
The COUNTIFS function can be used in the following versions of Microsoft Excel:- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007
Type of Excel Function
The COUNTIFS function can be used in Microsoft Excel as the following type of function:- Worksheet function (WS)
Example (as Worksheet Function)
Let's look at some Excel COUNTIFS function examples and explore how to use the COUNTIFS function as a worksheet function in Microsoft Excel:Based on the spreadsheet above, the following Excel COUNTIFS examples would return:
=COUNTIFS(A2:A9,"=2013") Result: 4 (applies 1 criteria) =COUNTIFS(A2:A9,"=2013",B2:B9,"=Oranges") Result: 2 (applies 2 criteria) =COUNTIFS(A2:A9,">=2009",B2:B9,"=Oranges", A2:A9,"<=2012") Result: 1 (applies 3 critiera) =COUNTIFS(A2:A9,">=2009",B2:B9,"=B*") Result: 2 (Uses the * wildcard to match on all products that start with B) =COUNTIFS(A2:A9,">=2009",B2:B9,"=B?nanas") Result: 2 (Uses the ? wildcard to match on a single character, ie: Bananas, Benanas, Binanas, Bonanas, and so on)
