COUNTIF and SUMIF
Your call with the sales team starts in ten minutes. They expect two numbers from you: how many headphone orders there were and what they add up to. All you have is an export from the CRM, where orders for different products are mixed together:
COUNT and SUM won't help here: they take every row. You need functions that check a condition first and take only the rows that match.
It's easier to see how they work on the first eight orders of the export: rows two through nine.
How many rows match: COUNTIF
COUNTIF has two arguments: the range to check and the condition. This formula counts the headphone orders:
=COUNTIF(A2:A9,"Headphones")
The condition goes in quotes because, for the function, it's text. The function applies it to each cell of the range in turn: is A2 "Headphones"? What about A3? That's why you don't put a cell address in the condition.
The function compares the whole cell: "Wireless headphones" doesn't match the condition "Headphones". It doesn't care about uppercase and lowercase letters.
A condition can also be a comparison. The sign and the number go together, also in quotes: ">5000" means "greater than 5,000". You can use the same signs as in ordinary comparisons, for example "<>Headphones" means "any product except headphones". A condition about the amount is checked against column B, and a condition about the product against column A:
What they add up to: SUMIF
SUMIF takes the same two arguments and adds a third: what to add up.
=SUMIF(A2:A9,"Headphones",B2:B9)
The function goes row by row. If column A says "Headphones", it takes the amount from the same row of column B. That's why both ranges must start and end on the same rows.
In the diagram, both functions go through the orders row by row. Switch the condition to "Over 5,000": the same column of amounts is both checked and added up.
AVERAGEIF calculates the average of the matching rows. Its arguments are the same as for SUMIF. For example, this is how you get the average watch order, which comes to $3,850:
=AVERAGEIF(A2:A9,"Watch",B2:B9)
The largest and the smallest order by a condition are found by MAXIFS and MINIFS, but in these functions the column of amounts comes first, and the range with the condition comes after it. For example, =MAXIFS(B2:B9,A2:A9,"Watch") returns the largest watch order, $4,300.
The report for the call
A coworker has already started a report below the orders, but both cells show 0. Fix the formulas:
The formulas work on eight rows. Now for the reason you started all this: the whole export. The formulas are the same, only the last row of the ranges changes:
A summary for every product
On the call, they may also ask about the other products. You don't need to write a separate formula with a name in quotes for each one: the condition can be a cell where that name is already written. You write the formula once and fill it down:
=SUMIF($A$2:$A$9,D2,$B$2:$B$9)
The ranges are locked with dollar signs so they don't shift when you fill the formula down. D2 has no dollar signs: one row down, the condition becomes D3, the next product.
Before the call, it's worth checking the summary: the SUM of column E must match the SUM of all the orders. If the summary is smaller, the export has a product that's missing from it.