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

By Ali, Founder, Excel Lessons

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.

Read more about this

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 cells in one range where a matching cell meets a condition.

In F2, type:

=SUMIF(B2:B7,"Ridgeway Farm",D2:D7)

It tests B2:B7 for Ridgeway Farm and adds D2:D7.

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 cells in one range where a matching cell meets a condition. In F2, type: =SUMIF(B2:B7,"Ridgeway Farm",D2:D7). It tests B2:B7 for Ridgeway Farm and adds D2:D7.

    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 dairy stalls' takings. Shape: =SUMIF(range, test, sum_range).

    Hint. Type column, "Dairy".

  4. 4

    A condition can be a comparison in quotes, like ">300". Leave out the third argument when both ranges match. In F4, total every stall that took more than 300. Shape: =SUMIF(range, test).

    Hint. Greater than 300, two arguments.

  5. 5

    A condition can come from a cell instead of being typed. In F5, total the trader named in B9. Remember: a cell reference needs no quotes.

    Hint. B9, no quotation marks.

  6. 6

    In F6, total the produce stalls' takings with SUMIF.

    Hint. Type column, "Produce", takings.