# COUNTIF, SUMIF and the IFS versions (Google Sheets lesson 2 of 17)

> Count or add only the rows that match, with conditions anyone can change from a cell.

Source: https://spreadsheetpractice.com/google-sheets/learn/countif-sumif · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets essentials"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `COUNTIF`, `SUMIF`, `COUNTIFS`, `SUMIFS`, `AVERAGEIF`
- **Access:** free
- **Key formula:** `=COUNTIF(C2:C,"East")`: Count rows where C is East

## 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

| Formula | What it does |
|---|---|
| `=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 rows |

## Watch 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

Solve them in the live grid at [COUNTIF, SUMIF and the IFS versions](https://spreadsheetpractice.com/google-sheets/learn/countif-sumif). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **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.
2. **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.
3. **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.

Previous: [Formulas in Google Sheets](https://spreadsheetpractice.com/google-sheets/learn/formulas-in-google-sheets) · Next: [VLOOKUP and XLOOKUP in Google Sheets](https://spreadsheetpractice.com/google-sheets/learn/vlookup-xlookup)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
