Level 3 · Several conditions with SUMIFS and COUNTIFS

SUMIF takes one condition. Susan’s question has two: the app and completed. For that you use the plural versions, COUNTIFS, SUMIFS and AVERAGEIFS, which take as many conditions as you need.

Conditions come in pairs

Every condition is a pair of arguments: a range to test, then what it must match.

=COUNTIFS(B2:B57,"app",C2:C57,"completed")

reads as “count the rows where B is app and C is completed”. Excel checks the pairs row by row: row 2 passes only if B2 is app and C2 is completed. On Black Friday that is 27 orders. Add a third pair and a row must pass three tests. The ranges must all have the same size, or the formula returns #VALUE!.

SUMIFS puts the sum range first

=SUMIFS(sum_range, range1, criteria1, range2, criteria2, …)

This is the trap of the whole family. SUMIF has the range to add up last; SUMIFS has it first, so the pairs can go on as long as needed:

Function	Arguments
SUMIF	range, criteria, sum_range
SUMIFS	sum_range, range1, criteria1, …
AVERAGEIFS	average_range, range1, criteria1, …
COUNTIFS	range1, criteria1, … (nothing to add up)

With the inputs in G1 and G2:

G4   =COUNTIFS(B2:B57,G1,C2:C57,G2)
G5   =SUMIFS(D2:D57,B2:B57,G1,C2:C57,G2)
G6   =AVERAGEIFS(D2:D57,B2:B57,G1,C2:C57,G2)

G5 shows 7,393.47 and G6 273.83. SUMIFS with a single pair does the same job as SUMIF, so many analysts use only the S versions: one argument order to remember.

What the status test changes

Leave the status out and the app looks better than it was. =COUNTIF(B2:B57,G1) counts 32 app orders, and =SUMIF(B2:B57,G1,D2:D57) adds up to 8,586.84. The extra 5 orders are 2 cancelled and 3 refunded ones: 1,193.37 that the store never kept. A dashboard that reports the bigger number overstates Black Friday app revenue by about 16%.

Check the average by hand

An average is a sum divided by a count, so G6 should equal G5/G4: 7,393.47 / 27 = 273.83. If they differ, the two formulas are not testing the same rows. AVERAGEIFS returns #DIV/0! when no row matches, for example after a typo such as complete in G2. That error is useful: it tells you the filter found nothing, where SUMIFS would quietly show 0.

Common mistakes

• SUMIF order in SUMIFS. =SUMIFS(B2:B57,G1,D2:D57,…) tests the wrong columns. In the S versions the sum range is always first.
• OR instead of AND. SUMIFS only does AND. For “app or web”, add two of them: =SUMIFS(D2:D57,B2:B57,"app")+SUMIFS(D2:D57,B2:B57,"web").
• Ranges that don’t line up. B2:B57 with C3:C58 compares each channel with the next row’s status. Same first row, same last row.
• Averaging the average. The average order value is total revenue over the number of orders, not an average of daily averages.