Level 2 · App orders with COUNTIF and SUMIF

SUM adds every number in a range. Most business questions are narrower: revenue from the app, orders over $100, returns marked damaged. The conditional functions add a test: they look at one column, keep the rows that pass, and count or add up only those. You met COUNTIF in the last level with numbers; here the test is on text, and SUMIF adds up money.

COUNTIF: how many rows match

=COUNTIF(range, criteria)

range is the column you test, criteria is what a cell must equal to be counted. On this sheet the channels are in B2:B30, so:

=COUNTIF(B2:B30,"app")

returns 17: seventeen of Sunday’s 29 completed orders came through the app. The match ignores case, so "App" and "APP" give the same 17.

SUMIF: add up the matching rows

=SUMIF(range, criteria, sum_range)

SUMIF tests range exactly like COUNTIF, then adds the cells of sum_range that sit on the matching rows. To add up the app subtotals:

=SUMIF(B2:B30,"app",C2:C30)

Excel pairs the ranges by position: the 1st cell of B2:B30 with the 1st cell of C2:C30, and so on. Order 12416 in row 2 is an app order, so its 127.95 is added; order 12419 in row 5 is a web order, so its 19.19 is not. The total is 8,344.73.

If you leave sum_range out, SUMIF adds the tested cells themselves, which is handy for a condition on the numbers: =SUMIF(C2:C30,">1000") adds the subtotals above 1,000. The next levels cover conditions like ">1000".

Put the condition in a cell

Andrew wants to switch channels. So instead of typing "app" into both formulas, point at the input cell G1:

G3   =COUNTIF(B2:B30,G1)
G4   =SUMIF(B2:B30,G1,C2:C30)

A cell reference needs no quotes: the criterion is whatever G1 holds. Type web into G1 and both answers change to the web orders. Nothing is filled down here, so the $ signs from chapter 1 are not needed.

Sanity-check the split

A conditional total should fit inside the whole. Two quick checks:

• the counts per channel add up to all the rows: 17 app + 10 web + 2 marketplace = 29 orders, the 29 rows in A2:A30;
• G4 is below =SUM(C2:C30), and it moves when you change G1.

If a count comes out 0, look at the data first: a channel stored as app with a trailing space does not equal app, and COUNTIF will not tell you why.

Common mistakes

• Swapping the ranges in SUMIF. The range you test comes first, the range you add up last. =SUMIF(C2:C30,G1,B2:B30) returns 0.
• Ranges of different sizes. =SUMIF(B2:B30,G1,C2:C25) pairs the wrong rows or stops early. Make both ranges cover the same rows.
• Quotes around a cell reference. =COUNTIF(B2:B30,"G1") looks for the text G1 and finds nothing.
• Typing the channel into the formula. It works today, and Andrew’s “now do web” means editing every formula.