Counting and summing by criteria: COUNTIF, SUMIF

In this lesson you will learn

Count matching cells with COUNTIF
Sum by a condition with SUMIF
Write criteria with comparisons, cell references and wildcards
Know COUNTIFS and SUMIFS for several conditions

Elvin's shop has sold dozens of products this week. Now he wants to know: how many electronics sales were there, and how much money did they bring in? Instead of scanning the list and adding on a calculator, two functions are enough: COUNTIF and SUMIF.

COUNTIF: counting by a condition

=COUNTIF(range, criteria)

where:

rangethe cells to check
criteriathe condition: a number, text, a comparison or a cell

Criteria	What it counts
`"Electronics"`	cells that say exactly “Electronics” (case does not matter)
`">=100"`	numbers 100 or greater
`"<>Home"`	anything other than “Home”
`E2`	values equal to the one in E2
`">"&E1`	values greater than the number in E1
`"pen"`	text containing “pen” (`*` means any characters)
`"B?"`	two-character text starting with B (`?` means one character)

SUMIF: summing by a condition

=SUMIF(range, criteria, [sum_range])

where:

rangethe cells the condition is checked against
criteriathe condition (as in COUNTIF)
sum_rangethe cells to add up; if omitted, range itself is summed

Sales by category

B2:B9 holds categories and C2:C9 the sales amounts in manat. Find the number of electronics sales and their total. The electronics sales are 250, 310 and 180 ₼.

Show solution

Count: =COUNTIF(B2:B9,"Electronics") → 3.
Total: =SUMIF(B2:B9,"Electronics",C2:C9) → 250 + 310 + 180 = 740 ₼.
The condition is checked in column B, and the C cells in the same rows are added.

Interactive

Loading simulation…

A week's sales (in manat) and a summary by condition.

Several conditions: COUNTIFS and SUMIFS

The functions ending in “S” check several conditions at once and include only rows where all of them are met. For example, the total of electronics sales of 200 ₼ or more: =SUMIFS(C2:C9,B2:B9,"Electronics",C2:C9,">=200") → 250 + 310 = 560 ₼. To count them: =COUNTIFS(B2:B9,"Electronics",C2:C9,">=200") → 2. For an average by condition there is AVERAGEIF.

How many students scored 90 or more: =COUNTIF(B2:B30,">=90").
All the money a family spent on food: =SUMIF(B2:B60,"Food",C2:C60).
Finding duplicate names: =COUNTIF($A$2:$A$100,A2)>1 returns TRUE when the name appears more than once in the list.
The average score of class 7A only: =AVERAGEIF(C2:C90,"7A",D2:D90).

Key points

=COUNTIF(range, criteria) counts the cells that match.
=SUMIF(range, criteria, sum_range) adds the values in rows that meet the condition.
A comparison criterion goes in quotes: ">=100"; with a cell: ">="&E1.
* stands for any number of characters, ? for exactly one.
COUNTIFS/SUMIFS handle several conditions; in SUMIFS the sum range comes first.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

What does =COUNTIF(C2:C9,">=100") calculate?