Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Spreadsheet Basics

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

The SUMIF Function

2:41 with Michael Watson

SUMIF is a very handy function. It combines the SUM and IF functions to sum up values based on conditions you specify.

Example Files

  • you can continue to use the spreadsheet from the last video, or
  • open a copy of this spreadsheet to catch up to this video

Reference

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    SUMIF is a great function I use fairly regularly. 0:00
    It combines sum and if functions to sum up values based on conditions you specify. 0:04
    Maybe a manager is looking at the spreadsheet of all their salespeople and 0:11
    wants to know how many of them sold more than $1 0:15
    million worth of business last month. 0:17
    She could use the SUMIF function to calculate that. 0:20
    Let's revisit the retail interview spreadsheet to do a SUMIF calculation. 0:23
    >> We're back in our retail transaction spreadsheet example now and we're trying 0:28
    to answer the question, what is the total value of all the jeans that we sold? 0:33
    We could use sorts and filters to figure this out or we can use the SUMIF function. 0:38
    So I'm selecting cell G2, 0:44
    hitting Enter and =SUMIF. 0:50
    And the syntax is first the range, then the criterion, then the sum range. 0:55
    So basically what range are we looking at? 1:00
    In this situation, it's gonna be the Items column. 1:04
    Then the criterion. 1:08
    And we're looking at jeans, so we want this to be equal to jeans. 1:09
    And then the sum range. 1:13
    In the sum range here, we wanna know the total value of all the jeans we sold, so 1:15
    we are gonna be summing up the price. 1:18
    So let's enter this into the function. 1:20
    I'm gonna select all the different items we sold as our range. 1:25
    Then the criterion is ="Jeans". 1:33
    Then the sum range, 1:42
    And I actually don't need to enter an equal sign here, that's redundant. 1:52
    So the total value of all the jeans that we sold is $1,400. 1:56
    So I could've done that by adding up all these different line items. 2:03
    And if we select all these cells, we can see down here that it's 1,200. 2:08
    So what is missing? 2:14
    We have two jeans down here. 2:17
    So using the SUMIF function helped correct for a potential human error. 2:20
    It's really important that the syntax you use is exact. 2:26
    If you have a misspelling here, nothing will be there. 2:30
    So make sure when you're using SUMIF functions that your criterion is exact. 2:34

    You need to sign up for Treehouse in order to download course files.

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up