The Excel Countifs function returns the number of rows within a table, 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:
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
The Countifs function allows the use of the following wildcards in textrelated 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.
E.g. 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:
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 casesensitive. So, for example, the text strings "TEXT" and "text" will be evaluated as equal.
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% 
If we want to know how many female test scores were greater than 60%, we could use the following formula:
which gives the result 4.
In this example, the formula has counted the number of rows where:
If we want to know how many science tests scores were less than 50%, we could use the formula:
which gives the result 2.
For further examples of the Excel Countifs function, see the Microsoft Office website.
If you get an error from the Excel Countifs function, this is likely to be the #VALUE! error:
#VALUE!   
Occurs if either:
