Spreadsheet Practice

Lesson 8 of 45, Logic & conditions

COUNTIFS, SUMIFS & AVERAGEIFS

Quick answer

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

Key formula
=SUMIFS(D2:D11,A2:A11,G2,B2:B11,G3) (Sum range first, then pairs)
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.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Two-condition total

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