The Excel COUNTIFS Function

Related Functions:
COUNT Function
COUNTIF Function

Basic Description

The Excel Countifs function returns the number of entries (within one or more supplied arrays), that satisfy a set of given criteria.

The function is new in Excel 2007, and so is not available in earlier versions of Excel.

The syntax of the Countifs function is :

COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Where the function arguments are as follows:

criteria_range1
[criteria_range2], ...
- Arrays of values (or ranges of cells containing values) to be tested against the respective criteria1, criteria2, ...

(The supplied criteria_range arrays must all have the same length)

criteria1,
[criteria2], ...
- The conditions to be tested against the values in criteria_range1, [criteria_range2], ...


Up to 127 pairs of criteria_range and criteria arguments can be supplied to the function.

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character
*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

Each of the supplied criteria can be either:

-         a numeric value (which may be an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2008, TRUE)
or
- a text string (eg. "Name", "Sunday")
or
- an expression (eg. ">12", "<>0")

and can be entered into the function either directly, as a value returned from another function, or as a reference to a cell containing a value.

Note that if your criteria is a text string or an expression, this must be supplied to the function in quotes.

Also note that the Excel Countifs function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be equal.


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.


Countifs Function Errors

The error that you are most likely to get from the Excel Countifs function is the #VALUE! error :

Common Error
#VALUE! - Occurs if the supplied criteria_range arrays do not all have equal length.


Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net