Count Occurrences Quickly Using COUNTIF

Alan Gurney
By Alan Gurney
27 Aug at 16:58

COUNTIF

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

=COUNTIF(B2:B12,"Strategy")

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:

  • The criteria should be entered as the second input and in quotation marks (e.g. “Strategy”).
  • The whole cell contents must match the criteria in order to be counted (e.g. if a cell had contained the text ‘Strategy Director’, it would not have been counted).
  • The matching is not case sensitive (e.g. a cell containing ‘strategy’ would have been counted).
  • If you are counting numbers you can choose not to include the “” marks.
  • It is a good habit to fix the ‘range’ reference as absolute (see Fixed References).
  • Count blanks by entering "" as the second argument (e.g. COUNTIF(C7:C14,"")).

Extra Power

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.