According to a recent article, Microsoft office is now installed on over one billion desktops around the world and Excel is the top spreadsheet application for data manipulation and analysis. With over 400 built-in functions to make your data analysis easier than ever, if you still do not know how to harness the power of Excel, then you should sign up for the Microsoft Excel 2013 Course Beginners/ Intermediate Training from Udemy.
This tutorial will show you how to use the SUMIFS function to sum data according to various criteria you determine. We will use fictitious data for our spreadsheet based on sales at a hardware store. The data contains various items sold, the salesperson responsible for the sale and the value of the items sold.
This is what our initial data looks like:
Let us assume we work for the company and the sales manager has asked us to extract various information from the spreadsheet. He would like to know:
How many nails were sold for the day
How many hammers John Sold
The value of the bolts Harry sold where the each sale included more than 5 bolts per sale
The SUMIF function is one of the best ways to extract this data from our spreadsheet.
To get the most out of this tutorial, we suggest that you have a good basic knowledge of how to create an Excel spreadsheet and how to add and format data within Excel. For a great introduction to Excel, sign up for the Excel – Basic Excel course now from Udemy.
How to Use Functions in Excel
Accessing functions in Excel couldn’t be simpler. All you need to do to access one of the functions in Excel, is to click the cell you want the answer to appear in and then either type in the name of the function you would like to use or select the function from the Formula tab.
To get the most out of the functions in Excel you need to know what the function does and how to use it in your spreadsheet. To learn some basic functions in Excel and how to apply them, read Excel Formulas: 10 Formulas That Helped Me Keep My Job.
The SUMIFS function sums all data within a selected range based on a number of different criteria.
To use the SUMIFS function you need to understand the syntax of the function or how it works. The syntax of the SUMIFS can be expressed as follows:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
Typing the SUMIFS function within a cell or selecting from the menu initiates the function. In other words by typing SUMIFS in the cell, Excel knows it must use the SUMIFS function. The sum_range criteria allows you to select the data that Excel must find the sum of, assuming the data meets the specified criteria. The criteria_range1 allows you to specify the first range Excel will use to set the criteria for the function and the criteria1 is the parameter Excel must search for. The next parameters appear in square brackets indicating that these parameters are optional parameters. You can include up to 127 different criteria for your SUMIFS function.
For some great Excel Tips and Tricks, check out Excel Tips and Tricks: Use Excel like a Pro on the Udemy blog.
Now that you know how the function works, let’s apply the SUMIFS function to our fictitious data.
How Many Nails were Sold for the Day?
To create a SUMIFS function to SUM all nails sold for the day, you need to select the cell you want the answer to appear in. So select D19 and then select the formula tab. The SUMIFS function is part of the Math & Trig functions available from Excel, so select Math & Trig and then click on the SUMIFS function. This will open up a wizard that you can use to enter your criteria for the function.
We want to add the nails that were sold so the Sum_range is D5:D17. We want to use the stock column to find out how many nails were sold so the criteria_range1 is B5:B17. We want Excel to search for the entry “Nails” and sum the number of nails sold type “Nails” into Criteria1.
This is the result of our first SUMIFS function to count how many nails were sold for the day:
How many Hammers John Sold
Our next example will use two parameters. We need to tell Excel to SUM how many hammers were sold but only the SUM of the hammers sold by John.
You need to use the SUMIFS function again, selecting your target cell first and then selecting the formulas tab. Select the Math & Trig functions and then select SUMIFS from the drop down menu. Once again the SUMIFS function wizard will open, allowing you to create the criteria for your SUMIFS function.
Here is what the completed wizard will look like:
The Sum_range is once again the units sold because we want Excel to count how many hammers John sold. The first criteria range is the stock type and the criteria to search for this time is “Hammer”. The second criteria we want Excel to search for is the salesman criteria. The range is C5:C17 and the criteria we need to enter is John.
Excel will now SUM all units that were sold that were hammers and that were sold by John. The output of the above function would look like this:
For an excellent training tutorial on Excel, sign up for the Microsoft Excel 2013 Training Tutorial course from Udemy.
The Value of the Bolts Harry Sold where the each Sale Included More than 5 Bolts per Sale
Our last example will use three search criteria. Use the steps above to open the function wizard and then add the following search criteria to the wizard:
Add Sum_range of E5:E17 to sum the value of the sale
Criteria_range1 is the Stock range so enter B5:B17
Type “Hammer” into Criteria1 to search for all Hammers sold
Criteria_Range2 is the salesman range so enter C5:C17
Criteria2 is Harry because we want to search for sales Harry made
Criteria_range3 is units because we want to search for sales Harry made where he sold more than five units per sale
Criteria3 is “>5” because we are searching for sales made that were greater than five units per sale.
This is what the completed wizard looks like:
And this is what the results of our SUMIFS function for the above criteria looks like:
The last example uses three criteria for the SUMIFS function. As mentioned above, you can use up to 127 different criteria for this function, so you can see how powerful this function is if you are working with vast amounts of data.
For a full course on Excel, sign up for the Microsoft Excel 2013 Advanced Online Excel Training Course from Udemy today and learn to harness the power of Excel to make your tasks simpler.