Excel Practice
Lessons Lesson 30

SUMIF

About this lesson

SUMIF adds the cells in one range where the matching cell in another range meets a condition. So a total can be limited to one trader, one type, or everything over a limit.

By the end you can

  • total only the rows that match a condition with SUMIF.
  • write a criterion as text, a number, or a comparison in quotation marks.

Practises SUMIF SUMIFS COUNTIF

The idea

Three arguments, in the order you would say them: where to look, what to look for, what to add. The condition can be a word, a number, a comparison written as text such as ">300", or a cell holding any of those. When the range being tested is the range being added, the third argument can be left off.

That short form is where the trouble starts. SUMIFS, the plural form, puts the range to add first. Learn SUMIF's order, then expect it to flip in the next lesson.

The mistake to watch for

Swapping the ranges and getting a number that looks right. SUMIF tests the first range and adds the third. The other way round, it tests the amounts and adds the names. That is 0, not an error. Where to look, what to look for, what to add, in that order.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

SUMIF adds up the cells in one range where the matching cell in another range meets a condition. SUMIF(range to test, condition, range to add).

In F2, type =SUMIF(B2:B7,"Ridgeway Farm",D2:D7).

It tests the traders in B2:B7 for Ridgeway Farm, and adds the takings in D2:D7 on the rows that match.

F2
Row A B C D E F G
1 Stall Trader Type Takings
2 S1 Ridgeway Farm Produce 412.5 Ridgeway total
3 S2 Tilly's Bakes Bakery 268 Dairy total
4 S3 Ridgeway Farm Produce 355.75 Over 300
5 S4 Kiln Lane Cheese Dairy 501.2 Named trader
6 S5 Tilly's Bakes Bakery 194.4 Produce total
7 S6 Ridgeway Farm Dairy 88.9
8
9 Trader Kiln Lane Cheese

Every step

  1. 1

    SUMIF adds up the cells in one range where the matching cell in another range meets a condition. SUMIF(range to test, condition, range to add). In F2, type =SUMIF(B2:B7,"Ridgeway Farm",D2:D7). It tests the traders in B2:B7 for Ridgeway Farm, and adds the takings in D2:D7 on the rows that match.

    Hint. Test B, match the name, add D.

  2. 2

    Read =SUMIF(C2:C7,"Bakery",D2:D7) and say what it returns. The condition is on the Type column this time.

    Hint. Two bakery rows.

  3. 3

    In F3, total the takings of the dairy stalls.

    Hint. Type column, "Dairy".

  4. 4

    A condition can be a comparison, written as text in quotation marks: ">300" means more than 300. And when the range tested is also the range added, the third argument can be left out. In F4, total the takings of every stall that took more than 300.

    Hint. ">300", two arguments.

  5. 5

    The condition can come from a cell rather than being typed. The trader named in B9 is the one the manager wants a figure for. In F5, total that trader's takings, pointing the condition at B9.

    Hint. B9, no quotation marks.

  6. 6

    In F6, write a formula using SUMIF that totals the produce stalls' takings.

    Hint. Type column, "Produce", takings.