Spreadsheet Practice

Lesson 8 of 29, Logic & conditions

COUNTIFS, SUMIFS & AVERAGEIFS

Filter by several conditions at once, driven by input cells.

Free10 minutes, 3 exercisesSUMIFSCOUNTIFSAVERAGEIFS

Watch it done

2 steps

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

How it works

The -IFS functions take any number of range/criteria pairs and include only rows that meet all of them.

The argument order differs from SUMIF: SUMIFS puts the range to add first. Point criteria at input cells instead of typing "East", and one formula answers every combination.

Syntax

=SUMIFS(D2:D11,A2:A11,G2,B2:B11,G3)Sum range first, then pairs
=COUNTIFS(A2:A11,G2,C2:C11,">=50")Count with two conditions
=AVERAGEIFS(D2:D11,B2:B11,G3)Average with conditions

Watch out for

  • SUMIF has the sum range last; SUMIFS has it first. Mixing them up is the most common -IFS error.
  • Every range in an -IFS function must be the same size.

The exercises

  1. Two-condition total. In G5, total the revenue for the region in G2 and the product in G3. Reference the input cells.
  2. Count with a threshold. In G6, count orders in the region from G2 with 50 or more units.
  3. Conditional average. In G7, average the revenue for the product in G3.

Two-condition total

In G5, total the revenue for the region in G2 and the product in G3. Reference the input cells.