← Google Sheets from Scratch: Formulas, QUERY, and Collaboration
Lesson
Lesson 3: Counting and adding with conditions: COUNTIF, SUMIF, SUMIFS
Count and sum the rows that meet a condition with COUNTIF, SUMIF, and SUMIFS (several conditions).
COUNTIF, SUMIF, and SUMIFS: counting and adding selectively
COUNTIF, SUMIF, and SUMIFS: counting and adding selectively
When you need to count or add up only the rows that meet a certain condition, three functions come to the rescue.
COUNTIF counts the cells in a range that meet a criterion: =COUNTIF(range, criterion). Example: =COUNTIF(B2:B100, "Moscow") counts how many times the word “Moscow” appears in column B. A text criterion or an expression goes in double quotes: "Moscow", ">100", "<>OK".
SUMIF adds up the cells of the sum_range whose matching cells in the criteria_range meet the criterion: =SUMIF(criteria_range, criterion, sum_range). Example: =SUMIF(A2:A50, "Electronics", C2:C50) adds up sales (column C) only for the “Electronics” category (column A).
SUMIFS lets you set several conditions at once, but its argument order differs from SUMIF — a common mistake: the sum range comes FIRST: =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, ...). Example: =SUMIFS(C2:C50, A2:A50, "Electronics", B2:B50, "Moscow") adds up only electronics from Moscow. Remember the rule: SUMIF puts the condition before the sum; SUMIFS puts the sum first.
Lesson notes
COUNTIF, SUMIF, and SUMIFS: counting and adding selectively
When you need to count or add up only the rows that meet a certain condition, three functions come to the rescue.
COUNTIF counts the cells in a range that meet a criterion: =COUNTIF(range, criterion). Example: =COUNTIF(B2:B100, "Moscow") counts how many times the word “Moscow” appears in column B. A text criterion or an expression goes in double quotes: "Moscow", ">100", "<>OK".
SUMIF adds up the cells of the sum_range whose matching cells in the criteria_range meet the criterion: =SUMIF(criteria_range, criterion, sum_range). Example: =SUMIF(A2:A50, "Electronics", C2:C50) adds up sales (column C) only for the “Electronics” category (column A).
SUMIFS lets you set several conditions at once, but its argument order differs from SUMIF — a common mistake: the sum range comes FIRST: =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, ...). Example: =SUMIFS(C2:C50, A2:A50, "Electronics", B2:B50, "Moscow") adds up only electronics from Moscow. Remember the rule: SUMIF puts the condition before the sum; SUMIFS puts the sum first.