COUNTIF

The COUNTIF function in Excel counts the number of cells that meet certain criteria within a specific range. This function will count how many people in your list live in a certain city, how many respondents identify as “female/male/non-binary,” or how many products were sold over 50 times today.

Syntax

The COUNTIF function exists of two components: the search range and the lookup value. So the syntax is:

=COUNTIF([range],[value])

Using COUNTIF

In our example we will use a short list of imaginary respondents to a survey:

Counting strings

Now let’s say we want to know how many of the respondents identify as “female.” Of course, in this short list we are able to count them manually, but in the case of many more respondents the COUNTIF function is very useful.

We will look for the string “female,” in cell range E2:E16, so the function is set up as follows:

=COUNTIF(E2:E16;"female")

Simple press ENTER to see the result (the total of respondents that identify as “female”).

Counting a “less than” value

Now let’s say, after counting the amount of respondents in each gender-category, we would like to know how many of the respondents are under 18 years old. In this case we will set up the function as follows:

=COUNTIF(D2:D16;"<18")

COUNTIF “equal to OR greater than”

And after pressing ENTER, we’ll do the same thing for respondents of 18 years old and over:

=COUNTIF(D2:D16;”>=18″)

NB: “equal to or greater than” is written as “>=”.

Our result again is visible after ENTER:

Adding up the results for gender and age shows us a regular outcome of 15, which matches the total of respondents. In case of larger numbers, calculating totals is always a safe practice.