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

Well done!

You have completed Spreadsheet Basics!

Sign up for Treehouse Back to Library
Preview
Video Player
00:00
00:00
00:00
  1. 2x 2x
  2. 1.75x 1.75x
  3. 1.5x 1.5x
  4. 1.25x 1.25x
  5. 1.1x 1.1x
  6. 1x 1x
  7. 0.75x 0.75x
  8. 0.5x 0.5x
Use Up/Down Arrow keys to increase or decrease volume.

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.

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