# 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 charactersCells 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 characterCells 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 