COUNTIF function is one of the most common excel functions used for counting numbers within a specific range. The number of cells in the range should match the supplied criteria.
Users need to specify single or multiple criteria to get the result. If you want to count cells based on multiple criteria, you can use a combination of COUNTIF functions or use COUNTIFS function. This tutorial will teach you how to use countif or countifs for single and multiple conditions.
COUNTIF function returns a numeric value and is applied to Microsoft Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, and Excel 2000.
Syntax: single criteria
COUNTIF (range, Criteria)
COUNTIF Parameters or Arguments
1. Range: This is a range of cells to be counted based on the criteria.
2. Criteria: It is the criteria used to determine which cells to be counted.
Example 1: From the table below, Count the number of cells with Bananas.
Put your cursor on cell D5 and click on FX to insert the function.
Enter function arguments
Click OK.
=COUNTIF (A2:A7,A5) – Counts the number of cells with Bananas from A2 to A7 and returns 1.
Example 2:
Count the number of cells with a value greater than 2 in cells C2 through to C7
The result is 2
Count cells with multiple criteria
A. Using COUNTIF function for Multiple Criteria
COUNTIF can be used to count two or more criteria by adding or subtracting several COUNTIF functions.
Syntax
=COUNTIF (range, Criteria)+ COUNTIF (range, Criteria)+..
Parameters
1. Range: This is a range of cells to be counted based on the criteria.
2. Criteria: It is the criteria used to determine which cells to be counted.
Example 1: COUNTIF with two conditions
Click OK
=COUNTIF(A2:A9,A4) + COUNTIF(A2:A9,A2)
The formula counts the number of apples (the value in cell A4) and lemons (cell A2) in cell A2 through to cell A9. The COUNTIF formula is used twice to specify multiple criteria.
B. Using COUNTIFS function for multiple criteria
COUNTIFS Function can also be used to specify multiple criteria. The "s" makes it plural. The function works the same as COUNTIF except that, the added criteria are separated by commas.
Syntax
=COUNTIFS (Criteria_range1, Criteria1, [Criteria_range2, Criteria2] …)
Parameters or arguments
1. Criteria_range1: The range of cells for which you want to evaluate against Criteria 1.
2. Criteria 1: it is the criteria used to evaluate for Criteria_range1 to determine the cells to count.
3. [criteria_range2]: is the range of cells you use to evaluate against criteria 2.
4. [Criteria]: Is the criteria which you want to evaluate for Criteria_range2 to determine which cells to count.
After entering the range of cells press the f4 key on the keyboard to lock the cells. Then press OK.
