← All lessons
Spreadsheets · Lesson 6 of 10

COUNTIF and SUMIF

COUNTIF(range, condition) counts the cells that match. The condition can be a value ("Food", 5) or a comparison in quotes (">20", "<>Done"). Matching text ignores upper and lower case, and * stands for any letters: "S*" matches anything starting with S.

SUMIF(range to check, condition, range to add) adds up the matching rows: =SUMIF(B2:B8,"Food",C2:C8) totals column C wherever column B says Food. AVERAGEIF works the same way.

With more than one condition, use COUNTIFS and SUMIFS. They take pairs of range and condition. Watch the order: SUMIFS puts the range to add first, =SUMIFS(C2:C8,B2:B8,"Food",C2:C8,">20").

To compare with a cell instead of a fixed value, join them with &: ">"&F1 means "more than whatever is in F1".

Practice

How many times was the money spent on Food? Count them in F1.

ABCDEF
1DateTypeAmountFood count
21 MarFood24
32 MarTravel12
43 MarFood31
55 MarFun40
66 MarTravel8
78 MarFood17
89 MarFun15

F1

More info

Quick reference

COUNTIF(range, condition)How many cells match.
SUMIF(range, condition, sum range)Adds the rows that match.
AVERAGEIF(range, condition, avg range)Averages the rows that match.
COUNTIFS / SUMIFSThe same with several conditions.
">20", "<>Done", "S*"Conditions: a comparison, not equal, starts with.

The practice answers

Other formulas that give the same results count too.

  1. How many times was the money spent on Food? Count them in F1. =COUNTIF(B2:B8,"Food")
  2. What did travel cost in total? Add it up in F1. =SUMIF(B2:B8,"Travel",C2:C8)
  3. How many payments were over 20? Count them in F1. =COUNTIF(C2:C8,">20")
  4. Add up only the Food payments over 20, in F1. =SUMIFS(C2:C8,B2:B8,"Food",C2:C8,">20")

An unofficial course. Not made by or affiliated with Microsoft; Excel is a trademark of Microsoft Corporation.