You are here

Home

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)

Explains the use of SUMIF function Excel

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.

Sumif Averageif formulas

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 ...)

Partner sites:

kosime.sk
Kosenie trávy, čistenie pozemkov a záhradnícke práce