Excel SUM and SUMIF Formula in Excel

Data Translates
219 followers
Hi everyone, my name is Kevin. Today I want to show you how you can use the sum function
in Microsoft Excel. The sum function helps you add lots of values together and you put the heavy
lifting on Excel to do that for you. As full disclosure before we jump into this, I work at
Microsoft as a full-time employee. My HR department requires me to say that whenever I talk about
Microsoft software. So the sum function, why would you want to use this? Well, I remember
early on when I started my career, this is something that just blew my mind. But I had
a co-worker call me into his office and I said, all right, let me go in there and see what's
going on. And he said, oh, Kevin, you know, I'm working on this spreadsheet here and it always
just takes me a long time to add up these different values. I kind of looked at him and I was like,
okay, you know, why does it take so long? You're using a spreadsheet, makes it pretty simple for
you. And then that's when I noticed that he had a calculator sitting alongside his keyboard and he
would go through the spreadsheet cell by cell. And when he saw the value, he typed it into his
calculator. Then he'd add the next value, hit the plus button, take the next value, add it up.
And then he would get the sum on his calculator and then he'd paste it. Once he got the sum,
he would paste it back into the spreadsheet. Now that is exactly what a spreadsheet is supposed
to do for you. And so today I want to basically pass on how you do this in Excel. So enough talk,
why don't we jump into Microsoft Excel and I'll show you exactly how to sum up values.
Here I am on my PC. I'm in Microsoft Excel. This is the latest version that comes with
office three 65. But even if you have an older version of Excel, you should also be able to do
this. I have three different columns. We're going to jump through one by one and how you
basically add some and then some if, and I'll get into what those mean. So in the first column,
I have these, I have basically the numbers one through 10 and we want to add them up. Well,
how do you do that? Well, that's in Excel. You could enter what's called a function.
I'm going to enter the equal sign. You always start a function with the equal sign. So first
we're going to do equals. And what I want to do is let's say I just want to add the first cell with
the second cell. Well, what I do is I click on the first cell, which is a three and then I'll
insert the plus sign and I'll do cell a four and then I could hit enter and that'll add the two
values. So one plus two is three, at least hopefully most of you agree with that. And that
gives me three. And what I could do is let's say I wanted to add this whole list. I could go through
and I could say, well, this cell plus this cell, plus this cell, plus this cell. And it works okay
when you only have 10 rows of data. However, imagine you had 10,000 rows of data. This would
take a little bit longer. So there I could press enter. And so adding is very good when you only
have a few values that you want to add. But as soon as you have a lot of values, it doesn't scale
all that well. And that's where the sum function comes in. So what is a sum function? Well,
why don't we first click on the function icon up here where I can insert a function and I'm
going to search for sum. You'll also see that it's a recent function that I use. So I'll just go
ahead and click on that. And what you could see here is it says, well, you know, tell us number
one that you want to sum. Well, I want that one. And then for number two, I want to sum that cell
number three. I want to sum this cell and I could go through all my numbers and just keep doing that
all the way down my list and then click okay. The problem though, is that too, if I click through
using this funk, the insert function tool, that also takes a little bit of time. Now, what you
could see is when it, when it created that function, it does the cell followed by a comma
followed by the next cell. So what I could do as well is let's type in the formulas. I'll do equals
and then some open parentheses, and then I could click on this cell followed by a comma. And then
I can go through and click a click on all the numbers that I want to sum. This is probably just
as much effort as entering a plus sign though. So how do I make it even easier? What if I just want
to sum this whole list? Well, what you could do is I'll type in the sum function. So equals sum
open parentheses. And what I can do is instead of entering one by one, what I could do is I'll click
here and I'll just drag it all the way down. So you see that I put a colon in between. What this
is saying is that I want to sum everything between cell D3 all the way down to cell D12.
And then I'll close the parentheses and hit enter. And what that did now is that summed up every
single value in here. So pretty cool. Now, what I can also do is I'll type in equals sum again.
And what I'm going to do is let's say I want the first three values and I want to sum the last
three values. So I selected this range. So D3 through D5. And now what I can do is I'll enter
a comma and now I can highlight the next three down here and then I'll close the parentheses.
And so what that did now is it sums these three values and then it also sums these three values.
And what's really neat is with the sum function, I can highlight a few values here, put in a comma,
highlight some here, put in a comma, highlight some here, highlight some here, and highlight
some here, and then close the parentheses. And you could add a lot of things very easily by using
the sum function. So pretty, pretty cool stuff in terms of addition. Now, what I also want to show
though, is what if you only want to sum up certain values? Like, let's say that I only want to sum my
favorite numbers between one and ten, and that happens to be four and eight. Actually, I just
made that up. But let's say those are my favorite numbers, four and eight. And I want to just sum
those values. Well, what I could do is, you know, once again, I could type in equals sum and I could
say, well, four is my favorite value and then comma eight's my favorite value. But once again, this
doesn't scale very well. Imagine that you had thousands and thousands of rows of data. Some are
favorites and some aren't favorites. Well, what comes in, what you can use here is sum if. So what I'm
going to do is, why don't we go to the insert function up here, and I'm going to type in sum if.
And then I'm going to click on go, and that brings up this sum if function. Let's go ahead and click
on OK. Now, I'm confronted with a few different options here. One of them is the range. Well, what
does range mean? Well, here it says the range is the range of cells you want evaluated. Well, what I
want to do is, I want to evaluate if this is my favorite. So for the range, why don't we select
this favorite column? This is my range that I'm evaluating against. The next option here says the
criteria, and this is the condition or criteria in the form of a number expression or text that
defines which cells will be added. Basically, what that means is, what am I looking for in these cells?
And what I want to do is, I want to sum up everything that's a favorite. So I'm going to
type in favorite, and then it says sum range. These are the values that I add. So it's my addition
range or my sum range. So what it's saying is, if it finds favorite here, it'll sum that value. If it
finds a favorite here, it'll sum this value. And here, I already get a preview that it equals 12.
So yes, I set this function up properly. And now I'll go ahead and click on OK. And here it says
that the sum of all my favorite numbers is 12. So it worked very nicely where it looked at
that criteria and my criteria range, and then it added up those values. But anyway, what I wanted
to do is, I wanted to show a few quick ways that you can add items in Excel using both just the
simple, basic, tried and true, plus sign, all the way down to sum and summing different sections of
the sheet. So you could sum a range here, another range elsewhere, all the way down to how to sum
things given certain criteria with sum if. Hopefully you're an addition expert now. If you feel like
you're pretty good at addition using Excel, please give this video a thumbs up. If you want to see
more videos like this in the future, other tutorial videos, hit that subscribe button. That way you'll
get a notification anytime new content like this comes out. And lastly, if there are other videos
to see, perhaps you want me to do a video on subtraction, leave a comment down below and I'll
add it to my list of videos to create in the future. And hey, that's all I have for you today.
Hope to see you next time. Bye.
