## You are here

### 11 - SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS

*SUMIF in Excel*

A useful formula when we sum the values based on required criteria.

For example, if we want to calculate the total profit of companies that employ more than 400 employees, the formula would looks following:

= SUMIF (C2: C13, "> 400", D2: D13)

Range - the range of cells, which specifies the number of employees (C2: C12)

Criteria - expression that we are looking for in this case "> 400"

Sum_range - the range of cells which specifies profits of companies (D2: D12)

*SUMIFS in Excel *is an extension of SUMIF () function which sums cells values based on multiple criteria.

= SUMIFS (sum_range, criteria_range1, Criteria1 [criteria_range2, criteria2] ...)

Example: In this case we will be interested in the total profit generated by U.S. companies which have more than 300 employees.

Sum_range - the range of cells which specifies profits of companies (D2: D13)

Criteria_range1 - cell range which specifies the number of employees (C2: C12)

Criteria1 - criteria which we are interested in - in this case "> 400"

Criteria_range2 - the range of cells which specifies the country of origin (B2: B12)

Criteria2 - criteria which we are interested in - in this case "USA"

The same logic can be applied for *AVERAGEIF in Excel* as well as *AVERAGEIFS in Excel* where instead of sums the output is average.

= AVERAGEIF (range, Criteria, [average_range])

= AVERAGEIFS ([average_range] criteria_range1, Criteria1, criteria_range2, criteria2 ...)