Do you have a data table where you need to pick all the highest, lowest, best, worst, or equal to 2? COUNTIF, and its bigger brother COUNTIFS formulas can help sum the cells of your choice! In this article, we're going to show you how to count the number of cells by an equal criteria.
Syntax
=COUNTIF(criteria range, criteria)
=COUNTIFS(criteria range, criteria, [ optional criteria range, optional criteria], …)
Steps
- Begin by typing in =COUNTIF(
- Select or type in the range reference that you want to apply the criteria against (i.e. $E$3:$E$10)
- Enter the criteria logic (i.e. "FL" alternatively you can enter the cell reference that contains the logic)
- Type in ) and press Enter to complete the formula
How
Both functions can be used to count values that meet a criteria. They search for a given criteria in the selected range, and then produce an array result of TRUE/FALSE based on the outcome. For example, searching for FL in $E$3:$E$10 range returns {TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE}. Number of TRUE values is what we're going to be using here.
=COUNTIF(E3:E10,"FL")
The COUNTIFS function can get more than one criteria range-criteria pair. Up to 127 range-criteria pairs can be used this way. It is important to note that each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other. The COUNTIFS function produces an array that contains Boolean values (TRUE/FALSE). Unlike the COUNTIF function, COUNTIFS produces array of TRUE/FALSE values for each pair, and combines them. For example, searching for 2017 in $G$3:$G$10 range returns {TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE}. Combining this array and the array from the "FL" search, we get the array of {TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE} that has 2 TRUE values.
=COUNTIFS(E3:E10,"FL")
=COUNTIFS(E3:E10,"FL",G3:G10,2017)
