Question: How can we use the SUMIF() and related functions?

Wizard: The SUMIF() function is a powerful extension of SUM(), the most popular spreadsheet function. In effect it is a conditional summation which enables you to sum the values in a range based on certain criteria. Other functions in the same family include COUNTIF() and AVERAGEIF().

The following example demonstrates a simple type of the SUMIF() and its related functions. The first part (argument) in the function is the range containing the values which will be added up if they satisfy a given criterion.

The second part is the criterion itself which, if it refers to logical or mathematical symbols, has to be enclosed in double quotation marks (").  

The other functions COUNTIF() and AVERAGEIF() use an identical syntax:

The next example demonstrates an extended type of the SUMIF() and its related functions. The first part (argument) in the function is the range containing the values which will be evaluated to arrive at the ones which satisfy a given criterion.

The second part is the criterion which, as before, if it refers to logical or mathematical symbols, has to be enclosed in double quotation marks ("). 

The third part is the range containing the values which will be added to the extent that their corresponding cell in the first part (range) satisfies the given criterion.

The AVERAGEIF() function uses an identical syntax, while the third part has no place in the COUNTIF() function:

 A more complicated related function is SUMIFS() which enables the use of multiple criteria and sum ranges.