# 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

*: this is a range whose cell values will be tested against the associated condition*

*criteria_range**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