4. COUNTIF, COUNTIFS, SUMIF and SUMIFS functions
COUNTIF Function
The function COUNTIF returns the number of cells that check a condition.
Syntax
COUNTIF(range, criteria)
Arguments
range : this is the range for which the cells meeting the condition will be counted
criteria : it is a condition on cells in the range specified as the first argument.
Result
The number of cells in range meeting the criteria condition
See the forms of writing criteria later in this topic.
A concrete example is given in the form of an exercise.
COUNTIFS Function
The function COUNTIFS counts the number of cells in the same way as the function COUNTIFF except that with COUNTIFS there is the possibility to specify several conditions.
Syntax
COUNTIF(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
criteria_range1 and its condition criteria1 are mandatory
We can add other ranges with their associated conditions: criteria_range2; criteria2; criteria_range3; criteria3; ...
Arguments
criteria_range : it is a range for which the cells meeting the associated condition will be counted
criteria : it is a condition against cells in the range specified in the argument just before.
Result
The ranges range_criteria1; range_criteria2... must have the same number of columns and rows. The conditions are tested first for the first cell of each range. If all conditions are true, then 1 is added to the result. If only one condition is false, then the count does not change. The second cells in each range are then tested and so on.
See the forms of writing criteria later in this topic.
A concrete example is given in the form of an exercise.
SUMIF Function
The SUMIF function returns the sum of values of cells that satisfy a condition.
Syntax
SUMIF(range, criteria [,sum_range])
Arguments
range : is the range whose cells will be checked against the criteria condition. If the third argument sum_range is omitted, then range is also used for the sum calculation
criteria : it is a condition relative to the cells of the range specified as the first argument
sum_range : optional argument. if it is specified, then it is used for the sum calculation.
Result
The sum of the range cell values that meet the criteria condition if the sum_range argument is omitted. Otherwise, the corresponding cells in the range sum_range are used for the sum
See the forms of writing criteria later in this topic.
A concrete example is given in the form of an exercise.
SUMIFS Function
The SUMIFS function counts the sum of cell values in the same way as the SUMIF function, except that with the SUMIFS function, it is possible to specify several conditions
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range, criteria_range1 and its condition criteria1 are mandatory
We can add other ranges with their associated conditions: criteria_range2; criteria2; criteria_range3; criteria3; ...
Arguments
sum_range : the range whose cell values will be used for the sum calculation
criteria_range : this is a range whose cell values will be tested against the associated condition
criteria : it is a condition against cell values in the range specified in the argument just before.
Result
The sum of the values of sum_range cells corresponding to the criteria_range cells that meet the associated conditions
See the forms of writing criteria later in this topic.
A concrete example is given in the form of an exercise.
Forms of writing the "criteria" argument
The forms of writing criteria are as follows:
| Form of the criteria argument |
Example | Cells counted | Comments |
| Fixed value | COUNTIF(A2:D21, "OK") | The cells in the range A2:D21 which have the value OK will be counted. | In the absence of a comparison operator, this is the equivalent of using the "=" operator |
| Cell address | COUNTIF(A2:D21, C1) | The cells of the range A2:D21 which have the same value as that of the cell C1 will be counted. | |
| Comparison operator and fixed value | COUNTIF(A2:D21, ">24") | The cells in the range A2:D21 which have a value greater than 24 will be counted. | Operator and value are written in quotes |
| Comparison operator and cell address | COUNTIF(A2:D21, ">" & C1) | The cells of the range A2:D21 which have a higher value than that of the cell C1 will be counted. | Do not write the cell address between quotes, otherwise it will be considered text. Write the address outside the quotes and use the concatenation operator. |
| Wildcard character * | COUNTIF(A2:D21, "*") | The cells of the range A2:D21 which have a text will be counted. | The wildcard character * matches zero, one or more characters Cells with number values are not counted with wildcard characters * nor ? |
| Wildcard character ? | COUNTIF(A2:D21, "??a*") | The cells in the range A2:D21 with the character "a" in the third position will be counted. | The wildcard ? matches one and only one character Cells with number values are not counted with wildcard characters * nor ? |
I remind you that for the four functions COUNTIF, COUNTIFS, SUMIF and SUMIFS the empty cells are not counted.
Other Excel functions for counting the number of cells
Other Excel functions allow you to count the number of cells:
COUNTA : determines the number of non-empty cells in one or more cell ranges.
Syntax : COUNTA(value1,value2,…)
COUNT : determines the number of cells containing numbers in one or more cell ranges. I remind you that dates are also considered in Excel as numbers.
Syntax : COUNT(value1,value2,…)
COUNTBLANK : determines the number of empty cells in a cell range.
Syntax : COUNTBLANK(range)
Arguments: The value and range arguments for these functions are the ranges where the cells will be counted.
Exercises
Exercise - Comparison of two lists
Exercise – COUNTIF, COUNTIFS, SUMIF and SUMIFS functions
Exercise - COUNTBLANK Function