Counting and adding with conditions
8 minutes · 4 steps · free, no sign-up
Real data is rarely one tidy total. This lesson shows how to add up or count only the rows that match a condition, such as one region or sales over a certain size.
- Total values that match a word with SUMIF
- Count rows with COUNTIF
- Write conditions like ">100"
- Find the middle value with MEDIAN
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceConditional functions
=SUMIF(range, condition, [sum_range])
=COUNTIF(range, condition)
=MEDIAN(range)
Conditions: "North" ">100" "<>South" ">=50"The key ideasSummary: Counting and adding with conditions
SUMIF looks through a range for cells that meet a condition, then adds the matching numbers from a second range. COUNTIF does the same but simply counts the matches. Conditions that use comparison operators go inside quotes, for example ">100".
MEDIAN returns the middle value once the numbers are sorted. It is often a better summary than the average when a few very large numbers could skew the result.
Watch out forCommon mistakes
- Leaving the quotes off a comparison. The condition
">100"must be in quotes. Without them, Sheets tries to read>100as part of the formula. - Ranges that do not line up. In
SUMIFthe range you test and the range you add should be the same height, or the rows will not match. - Invisible spaces in the data. A cell containing
"North "with a trailing space does not match"North". Clean it up withTRIMfirst.
Next stepTry it in the real Google Sheets
Real Sheets conditions are a little more powerful than the ones practised here. You can use wildcards, so "N*" matches anything starting with N and "?orth" matches any single first letter. SUMIF and COUNTIF ignore upper and lower case. The practice sheet does not support wildcards, so try them in a real spreadsheet.