Google Sheets lesson 2 of 11, Sheets essentials
COUNTIF, SUMIF and the IFS versions
Count or add only the rows that match, with conditions anyone can change from a cell.
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
COUNTIF counts the cells in a range that meet one condition. SUMIF checks one range and adds the matching rows of another. The condition is written in quotes: "East" for an exact match, ">=500" for a comparison. Text matching ignores case, so "east" matches "East".
The IFS versions take several conditions, and a row counts only when all of them are true: =SUMIFS(E2:E, C2:C, "East", D2:D, "Paid"). Watch the order. SUMIFS puts the range to add first; SUMIF puts it last. AVERAGEIF follows the SUMIF pattern. These functions behave the same way in Sheets and Excel.
Put the condition in a cell so anyone can change it. For an exact match, point at the cell: COUNTIF(C2:C, H2). For a comparison, join the operator to the cell with &: ">="&H9. Writing ">=H9" compares against the letters H9, not the cell, and counts nothing.
Wildcards match part of a text value. * stands for any run of characters and ? for exactly one, so "*Ltd" matches every name that ends in Ltd.
Syntax
=COUNTIF(C2:C,"East")Count rows where C is East=SUMIF(C2:C,H2,E2:E)Add E where C matches the value in H2=SUMIFS(E2:E,C2:C,H2,D2:D,"Paid")Add E where both conditions hold=COUNTIF(E2:E,">="&H9)A comparison built from a cell=SUMIF(B2:B,"*Ltd",E2:E)Wildcard: names that end in Ltd=AVERAGEIF(D2:D,"Paid",E2:E)Average of the matching rowsWatch out for
- ">=H9" in quotes is the text H9, not the cell. Build the condition as ">="&H9.
- SUMIF takes the range to add last; SUMIFS takes it first. Swapping them is the most common mistake.
- Every range in COUNTIFS and SUMIFS must cover the same rows. Mixing C2:C with E2:E9 returns an error.
- A stray space in "East " stops an exact match. If a count looks low, look for extra spaces in the data.
The exercises
- Count and add by region. H2 holds a region. In H3, count the invoices from that region. In H4, add up their amounts. Point at H2 instead of typing the region.
- Two conditions at once. In H6, add up the Paid invoices in the region in H2. In H7, count the Pending invoices with an amount over 1000.
- Conditions from cells and wildcards. In H10, count the invoices with an amount at or above the minimum in H9. In H11, add up the amounts billed to clients whose name ends in "Ltd". In H12, find the average amount of the Paid invoices.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Count and add by region
H2 holds a region. In H3, count the invoices from that region. In H4, add up their amounts. Point at H2 instead of typing the region.