In this episode of the Excel Learning Series, we are going to talk about the SumIF and SumIFS function in Excel. We also have AverageIF and AverageIFS functions in excel, however, we are not going to talk about that in today’s series but we are confident that you will be able to use them easily once you go through this article.
SumIF and SumIFS are functions in excel that allow us to sum data based on related criteria within the same data set.
The difference is that the SumIF function allows for imposing some single criteria on your data while the SumIFS allows for multiple criteria.
So for instance, if you would like to calculate a sum for a particular region (follow the picture for clear understanding)
We will begin with the below steps:
- Press =sumif or if you just type =sum, you will find multiple functions on the list, use the keyboard arrow keys, and select SUMIF.
- Then press Tab to complete the function.
The SUMIF function requires three arguments, Range, Criteria, and Sum_Range.
- Range – Range is where you want SUMIF to look for given criteria. For our case, its Column C
- Criteria – What do you want to look for as given criteria? In our case, it is Region Name in cell F5 and the rest in the same range for other cells.
- Sum_Range – The range of numbers to be added when the criteria is met.
Here is how the function will look: =SUMIF(C:C, F5, D:D). Basically, we are asking Excel to Sum the range of numbers from Column D wherever the value of F5 is found in the respective rows of Column C. Once you have the sum of one region, drag the selection using the drag handle on the lower right side to perform the calculation for other cells.
How to use SumIFS in Excel
In Excel, SumIFS will take multiple criteria to perform the sum. The function takes the first parameter as the range from where the numbers should be added, then it will ask for the range where you need to look for your first criteria followed with the criteria you would like to set. Now you can set multiple criteria as one by one that is select the range of cells where you want to look for the second criteria followed with the criteria and so on. You can set multiple criteria Ranges and criteria in this function. The most beautiful part here is, it will perform the sum only and only if all the given criteria are met.
In our case, we are asking Excel to perform the sum of column D each time it finds the region name of column J in column c and the department name of Kollam I in the range of column b. If the range and apartment of column I and J are found in the given range of column B and column c, the number from the column di will be considered for the calculation of SUM.
Excel SumIF and SumIFS Video Tutorial
Here is a video demonstration of this activity in case if you wish to watch and learn.
Click on the following button to download the file used in the demo to practice along with us.
Excel AverageIF and AverageIFS
The AverageIF and AverageIFS work in exactly the same manner as SumIF and SumIFS, however, here you will have the output as average instead of the conditional sum.
Try and explore the functions AverageIF an AverageIFS in Excel and see if you could use them with ease. In case you are getting stuck do not hesitate to ask by using the comments section below.
Excel Learning Series – TOC
- Excel 2016 – Basics – Find Sum, Average, Min and Max
- Excel 2016 Difference Between Formulas and Functions
- Excel 2016 Copy Formulas or Functions
- Microsoft Excel Auto Fill
- Excel 2016 Manage Views using Freeze Panes
- Microsoft Excel – Saving Workbooks
- Excel Upper and Lower Text Functions
- Excel 2016 – Text Function – Proper
- Excel Text Functions – Trim Function
- Excel – Text Function Left Function
- Excel Text Function – Right Function
- Excel Text Functions – MID Function
- Excel Concatenate Text Function
- Excel Nested Functions
- Excel Search Function
- Excel – Calculate Age using Date Function Today
- Calculate Tenure in Excel with Days Function
- Autofill Dates in Excel
- Formatting Dates in Excel
- Excel Date Format Shortcut
- Calculating Sum of Time in Excel
- Calculate Expiry Date in Excel 2016 – EDATE
- Find Age or Tenure using DATEDIF in Excel 2016
- RandBetween – Generate Random Dates in Excel 2016
- Excel Weekday Function
- Excel WeekNum and ISOWeekNum Functions
- Excel Networkdays and Excel Networkdays.INTL
- Excel Workday and Workdays INTL Functions
- Excel DATEVALUE AND TIMEVALUE
- Calculate Time Difference in Excel
- Calculate Quarter in Excel
- Rounding Day, Hour and Minutes in Excel
- Excel Pay Rate and Downtime Cost Calculation
- Excel Logical Functions IF, AND, OR
- Calculate Bonus in Excel Using IF Function
- Excel Learning – SumIF and SumIFS
- How to use Excel Count Functions
- How to use VLOOKUP in Excel
- How to vLookup in a different workbook in Excel
- VLookup for Multiple Sheets with Example
- Excel Custom Quarters
We hope you find this article useful. In case you are stuck and or need help, feel free to drop a comment below or submit your question. Do share it further and add your feedback about the article in the comments section.