Module 2 · The formulas you will actually use Lesson 4 of 15
Counting and adding on a condition
COUNTIF counts rows that match something, SUMIF adds the ones that match, and both take a range, a condition and — for SUMIF — the range to add up. AVERAGEIF does the same for averages.
The concept
SUMIF, COUNTIF, AVERAGEIF and their plural versions add and count only the rows that match: SUMIF(city, 'Pune', amount) totals Pune, COUNTIFS(city, 'Pune', type, 'remote') counts Pune remote jobs. They are how one sheet answers a hundred questions without a single pivot.
The plural forms take pairs — range, condition, range, condition — and every pair is an AND. Text conditions go in quotes, comparisons go in quotes too ('>1000'), and a condition can point at another cell so the report becomes a small form: type a city, every number updates.
=SUMIF(Table1[city], "Pune", Table1[amount])
=COUNTIF(Table1[type], "remote")
=AVERAGEIF(Table1[city], "Pune", Table1[amount])
=COUNTIFS(Table1[city], "Pune", Table1[type], "remote") -- AND
=SUMIFS(Table1[amount],
Table1[city], $H$1, -- condition from a cell
Table1[amount], ">1000")Common mistakes
The mistakes are a range that stops short of the last row, which is why tables are better than plain ranges, and a total row left inside the range being summed, which doubles your own figure. Check the range includes every row and excludes the total.
Worked example
=SUMIF(B2:B500,"North",D2:D500) totals the North region in one line. Doing it by hand with a filter is the same answer and four minutes of your life every time the data changes.
Your practice task
From a 500-row sheet, produce a region-wise total, a count of orders above ₹10,000 and an average order value per region — with formulas, not filters.
Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.
Key takeaway
SUMIF, COUNTIF, AVERAGEIF and their plural versions add and count only the rows that match: SUMIF(city, 'Pune', amount) totals Pune, COUNTIFS(city, 'Pune', type, 'remote') counts Pune remote jobs.
Keep your place in this course
A free account saves which lessons you have finished and unlocks the certificate. The lesson itself stays free to read.
Create free account or sign inCourse complete
You finished every lesson.
Your certificate is ready, and the projects you built are the proof to attach to an application.
Almost there
15 lessons left of 15
Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.