Course contents · Excel functions

Counting and conditional sums

Open the practice in the trainer →

A typical report: how many orders, how many are paid, what a particular customer has spent.

COUNTA(range)number of non-empty cells (numbers and text)
COUNT(range)number of cells containing numbers
COUNTIF(range, criteria)how many cells meet the condition
SUMIF(range, criteria, sum_range)sum by one condition
SUMIFS(sum_range, range1, criteria1, …)sum by several conditions — the sum range comes first

A criterion is text or a number ("Paid", 5) or a comparison in quotes: ">1000", "<>Cancelled". An asterisk matches any characters: "Smi*".

Another use of COUNTIF is finding values missing from another list: =COUNTIF(List_B, A2)=0.

Tasks in the trainer

  1. In G1 count all orders (use the Customer column).
  2. In G2 — how many orders already have an amount.
  3. In G3 — how many orders are paid.
  4. In G4 — the total of Smith's orders.
  5. In G5 — the total of Smith's paid orders only.

Solve the tasks in the trainer →