Spreadsheet Practice

Lesson 7 of 29, Logic & conditions

COUNTIF & SUMIF

Count or add only the rows that match a condition.

Free8 minutes, 3 exercisesCOUNTIFSUMIF

Watch it done

3 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

COUNTIF(range, criteria) counts cells in a range that match. SUMIF(range, criteria, sum_range) checks one column and adds the matching rows from another.

Criteria can be text ("East"), a comparison in quotes (">1000"), or a cell reference. To compare against a cell, join the operator on: ">"&G1.

Syntax

=COUNTIF(B2:B9,"East")Count matching text
=SUMIF(B2:B9,"West",D2:D9)Add amounts for West
=COUNTIF(D2:D9,">1000")Count with a comparison
=COUNTIF(D2:D9,">"&G1)Comparison against a cell

Watch out for

  • Comparison criteria go in quotes: ">1000", not >1000.
  • Text criteria ignore case: "east" matches East.

The exercises

  1. Count by region. In G2, count how many orders came from the East region.
  2. Sum by region. In G3, total the amounts for the West region.
  3. Count big orders. In G4, count orders with an amount over $1,000.

Count by region

In G2, count how many orders came from the East region.