Have you ever seen someone put data into Excel, then manually calculate the solution, and then type it into Excel? Perhaps you’ve even been this person! The good news is that I’m here to help you make sure that never happens again.
Folks (like you!) who are looking to learn more about the sum function in Excel are already on the road to redemption from committing the cardinal sin described above. If you’re Googling around about the sum function, it probably means that you already want to get better at Excel and stop doing double work. You want to learn about the sum function in Excel.
Download this Excel workbook, and I’ll walk you through some step-by-step examples. Don’t worry, I’ll include some screenshots and the completed workbook at the end so that you can check your work.
Before we even get to the SUM function, let’s make sure we’re on the same page. As you may know (or will find out), Excel offers a number of way to get to your final solution. We already know that you shouldn’t get to the final solution by adding up Here’s how you should NOT get to your solution.
In cell D10, type =D2+D3+D4+D5+D6+D7+D8+D9. You can actually type this out, or you can type the =’s and +’s and click on the actual cells. Either way, this is what you aren’t going to do, and I want you to experience why not!
Seems like a little too much work. Now in cell D10, type =SUM(D2:D9). Again, you might actually type this out, but what I prefer to do is type =SUM( and then click and drag across the area I want to sum, then press enter.
Now let’s get a little wild and introduce you to the SUMIF function. Imagine that you want to see how many purchases were made online, but you still don’t want to do the manual work. Well, welcome to the SUMIF formula. It’s a good name for a formula, because it does exactly what it says: it allows you to sum a group of cells, if certain criteria are met. In cell E12, type Online. Next, in cell D12, type =SUMIF( and you’ll see the following criteria:
- range
- criteria
- [sum_range]
– For this problem, this is the range of cells that tells you whether the sale was made Online or In Store. Choose A2:A9.
– Now, you want to choose which criteria to match. Since you want the Online sales only, choose cell E12.
– Next, what do you want to sum? Well, you want to sum the sales amount. Choose D2:D9, and now, for any row where the order method type is Online, the sales amount will be added to the sum!
There’s much more you can do with the SUMIF formula, but let’s leave it there for now.
Here’s the completed Excel workbook for your reference.
How are you feeling now that you’ve mastered the SUM formula and started your first foray into the SUMIF formula? Share in the comments! Don’t forget to check out Magoosh’s Master Excel: Beginner to Advanced series either.
Comments are closed.