Using SUMIF, COUNTIF and Similar “IF” Functions

Do you need totals, an average, or a count of cells in a range, but only under certain circumstances?  Then one of the several statistical “IF” functions will be useful.   They let you use a criterion, or several criteria, to include or ignore chosen cells.

They are:
COUNTIF, COUNTIFS count those cells in a range that match a given criterion or criteria.
SUMIF and SUMIFS total only those cells in a range that match a given criterion or criteria.
AVERAGEIF, AVERAGEIFS, average numbers with that match a given criterion or criteria.

IF (Singular) Version

The IF (singular) version takes one criterion.  Syntax:
COUNTIF(range, criterion)
SUMIF(range, criterion, [sum_range])
AVERAGEIF(range, criterion, [average_range])

The arguments are:

Range (required): The range of cells to which to apply the criterion.

Criterion (required): The criterion to apply to each cell in “range”.  If the criterion computes to TRUE, then the program includes that cell in the calculation.

The criterion can be a number, expression, a cell reference, text, or a function that defines which cells to include. You can use Wildcard characters:  A question mark (?) matches any single character, an asterisk (*) matches any number of any characters. To find an actual question mark or asterisk, use a tilde (~) before the character.

Examples of valid criteria: 42, “>=42”, A6, “44?”, “widget*”, “*~?” (anything ending in a literal question mark), or TODAY() (to match today’s date without a time fraction).  Note the quotation marks (“”) around any criterion that is not is numeric.

sum_range (optional): The cells to add, if you want to add cells other than those in the “range”. If you omit sum_range, Excel adds the cells in “range”.

average_range (optional): The range of cells to average. If omitted, Excel averages the cells in “range”.

sum_range and average_range must be the same size and shape as “range”.

Example Using COUNTIF

In column J in the example below (documented in column K), we use COUNTIF.  These formulae count the number of projects with Red, Amber, and Green status respectively.

The Red, Amber, and Green cell backgrounds are set using conditional formatting:

Example Using SUMIF

In column J in the example below (documented in column K), we use SUMIF. These formulae total the workdays planned for the projects segmented by Red, Amber, and Green status respectively.

Let’s look at the formula =SUMIF($B$4:$B$13, I4, $G$4:$G$13) in cell J4.  This formula looks in range $B$4:$B$13 to match the criterion =I4 (“R”).  It then sums corresponding cells from sum_range $G$4:$G$13 where the criterion matches.  The copies below use the criteria on their corresponding rows (I5 =“A” and I6 =”G”) to process the same range and sum_range.

IFS (Plural) Version

The IFS (plural) version can take many criteria.  Syntax:
COUNTIFS(criterion_range1, criterion1, [criterion_range2, criterion2]…)
SUMIFS(sum_range, criterion_range1, criterion1, [criterion_range2, criterion2], …)
AVERAGEIFS(average_range, criterion_range1, criterion1, [criterion_range2, criterion2], …)

Again, the arguments are very similar:

Sum_range (required) and Average_range (required). The range to sum or average, respectively.

Criterion_range1 (required): The range to test using Criterion1.

Criterion1 (required): The criterion that defines which cells in Criterion_range1 will be used.  As above, examples of valid criteria are: 42, “>=42”, A6, “44?”, “widget*”, “*~?” or TODAY().  Note the quotation marks (“”) around any criterion that is not is numeric.

Criterion_range1 and Criterion1 set up a search pair to search a range for that specific criterion. Once items in the range are found, their corresponding values are included in the count, sum, or average.

Criterion_range2, criterion2, …  (optional):  Additional ranges and their associated criteria. You can enter up to 127 range/criterion pairs.

Criterion_range1, criterion_range2, …    Criterion_range1 is obligatory, subsequent criterion_ranges are optional.  This allows each range-criterion pair to have a different Criterion_range.  There is no problem if some or all of the criterion ranges are the same.

Criterion1, criterion2, …    Criterion1 is obligatory, subsequent criteria are optional.  Each criterion can be in the form of a number, expression, cell reference, or text that define which cells will be processed.

Criteria are cumulative.  That is, only items that match all the sets of criteria will be included in the count, sum, or average.

Example Using SUMIFS

We use SUMIFS in cells G4:I8 in the example above. We show Cell G4 contents in cell F11 and on the formula bar.  This SUMIFS formula totals the budgets for the projects segmented by Red, Amber, and Green status (columns), and by Area (rows).

Let’s look at the formula =SUMIFS($D$4:$D$13, $A$4:$A$13,G$3, $B$4:$B$13,$F4) in cell G4.  This formula sums cells from the range $D$4:$D$13.  It looks in the range $A$4:$A$13, to match the criterion = G$3 (“R” in this case).  It also looks in the range $B$4:$B$13, to match the criterion = $F4 (“AFR” in this case).

The copies of the SUMIFS below and to the right use the criteria in their corresponding status columns and area rows.

As an aside, another way to create the table of budget totalled per status and area, would be to use a pivot table.  More about that in a later post.