The sum range moves first in the plural form

One trap, two functions

SUMIF and SUMIFS do the same job — add up the values whose rows meet a condition — and they put the range to add in opposite positions. It is the single most-tested formula fact in the course, because a SUMIFS written in SUMIF order returns a wrong number, not an error.

Worked example — Ontario, then Ontario online

The singular form, from Lesson 4.7, tests one range and adds another, in that order:

=SUMIF($B$2:$B$13, "ON", $D$2:$D$13)

→ 1995. Range to test, criterion, range to add. The third argument is optional; when it is left out, SUMIF adds the tested range itself (=SUMIF(D2:D13, ">500") sums the amounts over 500).

The plural form was designed for several conditions, and because there can be any number of range, criterion pairs, the range to add has to come first so it is never ambiguous:

=SUMIFS($D$2:$D$13, $B$2:$B$13, "ON", $C$2:$C$13, "Online")

→ 930 (orders 1001 and 1006: 420 + 510). Sum range, then range–criterion pairs — the same pairs COUNTIFS uses. AVERAGEIFS has exactly the SUMIFS shape:

=AVERAGEIFS($D$2:$D$13, $B$2:$B$13, "ON", $C$2:$C$13, "Online")

→ 465 (930 / 2). The singular AVERAGEIF follows SUMIF: range, criterion, average_range.

Write the plural in the singular order — =SUMIFS($B$2:$B$13, "ON", $D$2:$D$13, …) — and Sheets tries to add up province codes against a criterion of amounts. The result is 0 or a #VALUE!, and with numbers in both columns it can be a plausible wrong total. That is why the diagram lines the two argument lists up.

The R side: subset, then summarise

R splits the operation into two steps that Module 9 formalises: select the rows with a logical vector in square brackets, then apply the summary function.

> sum(orders$amount[orders$prov == "ON"])
[1] 1995
> sum(orders$amount[orders$prov == "ON" & orders$channel == "Online"])
[1] 930
> mean(orders$amount[orders$prov == "ON" & orders$channel == "Online"])
[1] 465

orders$amount[condition] is the amounts where the condition holds; wrap it in sum, mean, length, max as needed. There is no argument order to remember, because the condition is the same expression whatever the summary.

Nothing matches

Ask for Manitoba, which has no orders. =SUMIFS(…, $B$2:$B$13, "MB") returns 0 — a sum over no rows — and R's sum(orders$amount[orders$prov == "MB"]) is also 0. But an average over no rows is a division by zero: AVERAGEIFS returns #DIV/0!, and R returns NaN (not a number), not NA:

> mean(orders$amount[orders$prov == "MB"])
[1] NaN

A #DIV/0! in a conditional-average column almost always means a category with no rows, not a broken formula.

The pivot connection

Module 7 builds a pivot with prov in Rows, channel in Columns and amount in Values summarised by Sum. The cell at ON × Online is 930 — the SUMIFS above. Every pivot cell is a SUMIFS (or COUNTIFS, or AVERAGEIFS) whose criteria are the row and column labels, which is why a quiz can ask for "the formula that reproduces this pivot cell".

Type three, then fill the worksheet

Type the SUMIF, the SUMIFS and the R two-criteria sum. The worksheet asks for the three values; the code block reproduces the two-criteria sum.

CRISP-DM: a conditional aggregate is data understanding → explore data.