Course outline / Mathematical functions
Lesson 18 of 75 · Intermediate · 6 exercises
Totals with conditions
Use SUMIF, COUNTIF and SUMIFS to total only the rows you want.
What you'll practise
- SUMIF with a number test
- SUMIF with a separate sum range
- Count matching rows with COUNTIF
- Match part of the text with *
- Total with a wildcard
- Two conditions with SUMIFS
SUMIF with a number test
SUMIF adds only the cells that pass a test. The test goes in double quotes, such as ">3" for "more than 3".
=SUMIF(range, test)
In cell E2, total the units in B2:B6 for orders of more than 3 units.
SUMIF with a separate sum range
SUMIF can also test one column and add up another. The third input is the range to add.
=SUMIF(test range, test, sum range)
In cell E2, total the Amount (C2:C6) for rows where the Product (A2:A6) is Tea.
Count matching rows with COUNTIF
COUNTIF works like SUMIF, but counts the cells that pass the test instead of adding them.
=COUNTIF(range, test)
In cell E2, count how many orders in A2:A6 are for Coffee.
Match part of the text with *
Criteria can contain wildcards, which stand in for characters you don't know:
* matches any number of characters, so "*tea" matches anything ending in tea.
? matches exactly one character, so "?at" matches cat and hat.
To search for a real * or ?, put a tilde in front of it: ~*.
In cell E2, use COUNTIF to count the products in A2:A6 that end in "tea".
Total with a wildcard
Wildcards work the same way in SUMIF. Here, every kind of tea counts towards the total, whatever comes before the word.
In cell E2, use SUMIF to add up the amounts in C2:C6 for products ending in "tea".
Two conditions with SUMIFS
SUMIFS handles several tests at once. Watch out: the sum range comes first this time, then each range is followed by its test.
=SUMIFS(sum range, range 1, test 1, range 2, test 2)
In cell E2, total the Amount (C2:C6) for Coffee orders (A2:A6) with more than 5 units (B2:B6).
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.