Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 3, Live 2
SUMIF, COUNTIF, AVERAGEIF and Their S Versions
Most business questions sound like "how much, for this group?" or "how many, that match this?". Six functions answer almost all of them, each in one formula.
By the end of this class you can
- Explain conditional aggregation in one sentence
- Total, count and average the rows that meet one condition with SUMIF, COUNTIF and AVERAGEIF
- Do the same for several conditions at once with SUMIFS, COUNTIFS and AVERAGEIFS
- Write criteria as text, comparisons, cell references, dates and wildcards
- Build a summary table with anchored ranges and check it adds up
Start from the file you saved in Virtual 1: Revenue in M, Size in N, Zone in O. Every total today adds up Revenue, which already leaves out the sales whose quantity is unknown. Answers go in a summary area in columns Q and R, beside the data.
1. Conditional Aggregation
Definition
Aggregating means combining many values into one: a total, a count, an average. Conditional aggregation does it only for the rows that meet a condition. SUM adds every sale; SUMIF adds only the Port Harcourt sales.
Real-Life Scenario
A shop owner tipping the day's receipts onto the counter and saying "add up only the ones for laptops". She does not move the other receipts or throw them away; she just skips them while she adds. Tomorrow she can ask "only TVs" of the same pile.
The Six Functions
| One condition | One or more conditions | |
|---|---|---|
| Total | SUMIF | SUMIFS |
| Count | COUNTIF | COUNTIFS |
| Average | AVERAGEIF | AVERAGEIFS |
Learn one row of the table and you have learnt the pattern for all three. We start with totals.
2. SUMIF
Definition
SUMIF(range, criteria, sum_range): look down range, find the rows that match criteria, and add up the sum_range values on those rows.
Example 1: Total Revenue for the Port Harcourt Branch
Port Harcourt sold N148,290,000, which is 14% of all TechPlug revenue. Read the formula as a sentence: look down Branch (J), find "Port Harcourt", add up Revenue (M) on those rows. The two ranges must be the same size and start on the same row, so row 2 of one lines up with row 2 of the other.
The Kinds of Criteria
The criteria can be written five ways. Each is shown below with a real TechPlug answer.
Three rules hide in that list. Comparisons go in quotes: ">1000000", not >1000000. To compare with a cell, join the operator to it with &: ">"&R13. And when the range to test and the range to add are the same column, as in the second row, you can leave sum_range out. In that example R12 holds Kano and R13 holds 3,000,000; change R12 to Jos and the answer changes instantly, which is how dashboards with a "choose a branch" box are built.
| Wildcard | Means | Example |
|---|---|---|
| * | Any number of characters, including none | "*TV*" matches any name containing TV |
| ? | Exactly one character | "Ka?o" matches Kano |
SUMIF treats "kano", "Kano" and "KANO" as the same, but " Kano" with spaces is different. Week 2's cleaning is why the branch totals above are right.
Picture the shop owner's counter again: one pile of receipts, and a sticky note saying "laptops only". SUMIF is the sticky note.
3. SUMIFS
Definition
SUMIFS(sum_range, range 1, criteria 1, range 2, criteria 2, ...): add up sum_range on the rows that match every pair. You can add as many pairs as you need.
In SUMIF the range to add comes last. In SUMIFS it comes first. This trips up everyone once. A good habit: use SUMIFS even for one condition, so you only ever learn one order.
Example: Port Harcourt Revenue From Corporate Customers Only
Of Port Harcourt's N148,290,000, N41,877,000 came from corporate customers: 28%. The first line is SUMIFS with one pair, giving the same answer as SUMIF did. The second adds a second pair, and only rows that pass both are added.
A Range of Dates
Two conditions on the same column give you a range. Dates are compared like numbers, and DATE(year, month, day) writes one inside a formula:
February brought in N337,417,000. (In Week 1 you saw why it is the lowest month: it is the shortest.) The same pattern, with the dates changed, gives any week, month or quarter.
4. COUNTIF and COUNTIFS
Definition
COUNTIF(range, criteria) counts the rows that match. COUNTIFS(range 1, criteria 1, range 2, criteria 2, ...) counts the rows that match every pair. There is nothing to add up, so there is no sum_range.
Real-Life Scenario
A teacher asking "hands up if you scored above 70" (COUNTIF), then "keep your hand up only if you also submitted every assignment" (COUNTIFS).
382 of the 1,050 sales were wholesale (36%), 40 of them in Kano, and 148 sales were for 8 units or more.
=COUNTIF(K2:K1051,"Unknown") should give 15: the blanks Step 5 filled in. Compare any COUNTIF with the filter's status bar count; if they disagree, a spelling you have not cleaned is hiding in the column.
5. AVERAGEIF and AVERAGEIFS
Definition
AVERAGEIF(range, criteria, average_range) averages the matching rows. AVERAGEIFS(average_range, range 1, criteria 1, ...) does it for several conditions, and like SUMIFS puts the range to average first. Both skip empty Revenue cells, which is exactly what we want.
A laptop sale is worth N1,786,113.99 on average, and a corporate laptop sale N1,803,600. The third line asks about a branch TechPlug does not have, and the answer is #DIV/0!: no rows matched, and an average of nothing means dividing by zero. A typo in the criteria gives the same error, so #DIV/0! from an AVERAGEIF usually means "check your spelling".
6. Overall Example: A Summary Table by Zone
One formula per question is useful. One formula per question per zone, in a table, is a report. Type the five zones in Q2:Q6, then one formula in each column of row 2, and fill them down. The data ranges must not move as you fill down, so they are locked with $ on the row numbers. The zone, Q2, must move, so it is not.
| Cell | Formula typed in row 2, then filled down to row 6 |
|---|---|
| R2 | =SUMIFS(M$2:M$1051,O$2:O$1051,Q2) |
| S2 | =COUNTIFS(O$2:O$1051,Q2) |
| T2 | =AVERAGEIFS(M$2:M$1051,O$2:O$1051,Q2) |
| U2 | =R2/SUM(R$2:R$6) |
The total row is the check: N1,067,343,000 and 1,050 sales, exactly the whole company. (Sales counts every sale, including the 25 whose revenue is unknown; that is why it is not simply revenue divided by average.) If a zone were misspelt in column Q, its row would show 0 and the total would fall short, and you would know at once. The South West (Lagos Island, Lagos Mainland and Ibadan) brings in 30% of everything.
Forgetting the $. Fill =SUMIFS(M2:M1051,O2:O1051,Q2) down one row and it becomes M3:M1052 and O3:O1052: the first sale drops out of every row below the first. The totals look plausible and are wrong. Lock the data ranges, leave the criteria cell free.
Picture a register with five pages, one per zone. One teacher writes the same questions at the top of every page; each page answers for its own zone. At the back, the totals page must match the head count of the whole school.
Practical Exercises
Exercise 1: Very Easy
How many sales were paid by Transfer? Use COUNTIF.
Exercise 2: Simple Application
What was the total revenue of the Televisions category? Use SUMIFS.
Exercise 3: Combination
What was the average value of a Retail sale in Lagos Mainland? Use AVERAGEIFS.
Exercise 4: A Small Real-World Problem
Build the zone table above for Category instead: five rows, the same four formulas, and a total row that matches the company.
Answers to the exercises
Exercise 1: 270. Exercise 2: N244,880,000. Exercise 3: N1,145,612.90. Exercise 4: the category table must total N1,067,343,000 and 1,050 sales.
Checkpoint
Q1. What is the difference in argument order between SUMIF and SUMIFS?
SUMIF puts the range to add last; SUMIFS puts it first.
Q2. How do you write "greater than the value in R13" as a criteria?
">"&R13
Q3. AVERAGEIF returns #DIV/0!. What is the most likely cause?
No rows matched, often because the criteria is misspelt or the value does not exist.
Q4. Why are the data ranges in the summary table written M$2:M$1051?
So they stay on the same rows when the formula is filled down; only the criteria cell Q2 should move.
Q5. Does SUMIF see "KANO" as Kano? Does it see " Kano"?
Yes to the first, no to the second.
Tick Before You Move On
- ☐ I can explain conditional aggregation as "total, count or average, only where..."
- ☐ I can write SUMIF and SUMIFS and remember the argument order
- ☐ I can write criteria as text, a comparison, a cell reference, a date range and a wildcard
- ☐ I can count with COUNTIF and COUNTIFS, and average with AVERAGEIF and AVERAGEIFS
- ☐ I know what #DIV/0! from AVERAGEIF usually means
- ☐ I built a zone summary table whose total matches the company
Quick Recap
Conditional aggregation totals, counts or averages only the rows that match. SUMIF puts the range to add last; SUMIFS, COUNTIFS and AVERAGEIFS put it first and take many pairs. Criteria can be text, a comparison in quotes, a cell, an operator joined to a cell, or a wildcard. Lock data ranges with $ when filling a summary table, and add a total row to check it.
Final Scenario
TechPlug's managing director asks three questions in one message: what did Port Harcourt sell, how many wholesale sales did we make, and are laptops worth their shelf space?
You answer in three lines, each from one formula: Port Harcourt sold N148,290,000; 382 sales were wholesale; a laptop sale averages N1,786,114, against N1,041,310 for a sale overall.
Then you add one line she did not ask for: "A laptop sale we miss costs more than a missed sale of anything else." That last line is the analysis. The formulas just made it quick.
One formula per question, and a total row to prove the table.
Next, Week 3, Virtual 2: five business questions on your own, then the Week 3 pod task: a Revenue column and eight business questions on Verdant Foods.