Take a look at the table below.
If we want to know how many employees there are in each team, we could manually count them if the database was small. If it was a bit bigger, we could sort the rows by team name (see Basic Sorting & Filtering), which would make counting manually easier. But if the database was very large, or it was subject to periodic updates, the manual method would be too time-consuming.
The COUNTIF() formula counts cells just as COUNT() does (see Totals and Counts), but only if their contents are something specific, using a range and a single criterion that you indicate. For example, you can count all the cells that start with a certain letter, or that contain a number that is larger or smaller than a number you specify.
In the spreadsheet shown above, specifying
will take the range B2:B12 (the first input) and count how many cells contain exactly the entry 'Strategy'. The result will be 3.
Excel calls the set of cells being counted over (B2:B12 in this example) the ‘range’, and the text that selects which of those cells will be counted (in this example the contents “Strategy”) the ‘criteria’.
There are a few things to note in using COUNTIF() in this way:
COUNTIF becomes even more powerful if the ‘criteria’ is held in a cell rather than entered as text – like “Strategy” – since it allows counting over lots of different categories in the data set.