Countif excel method

Excel Countifs Function Examples

The spreadsheet below shows a set of exam results for a class of students.

The Countifs function can be used to find the number of rows in the data set that satisfy conditions relating to the subject, score and gender.

This is shown in the examples below.

A B C D
1 Name Gender Subject Score
2 Anne Female Maths 63%
3 Anne Female English 78%
4 Anne Female Science 39%
5 Carl Male Maths 55%
6 Carl Male English 71%
7 Carl Male Science 51%
8 Kath Female Maths 78%
9 Kath Female English 81%
10 Kath Female Science 49%
11 Tony Male Maths 35%
12 Tony Male English 69%
13 Tony Male Science 65%
Example 1

If we want to know how many female test scores were greater than 60%, we could use the following formula:

=COUNTIFS( B2:B13, "Female", D2:D13, ">60%" )

which gives the result 4.

In this example, the formula has counted the number of rows where:

- The entry in column B is equal to "Female"
and
- The entry in column D is greater than "60%"

Example 2

If we want to know how many science tests scores were less than 50%, we could use the formula:

=COUNTIFS( C2:C13, "Science", D2:D13, "<50%" )

which gives the result 2.

Further examples of the Excel Countifs function are provided on the Microsoft Office website.