Excel Learning – SumIF and SumIFS

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.
How to SumIF in Excel

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.

How to SumIFS in Excel

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.

Excel SumIF and SumIFS Video Tutorial

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

  1. Excel 2016 – Basics – Find Sum, Average, Min and Max
  2. Excel 2016 Difference Between Formulas and Functions
  3. Excel 2016 Copy Formulas or Functions
  4. Microsoft Excel Auto Fill
  5. Excel 2016 Manage Views using Freeze Panes
  6. Microsoft Excel – Saving Workbooks
  7. Excel Upper and Lower Text Functions
  8. Excel 2016 – Text Function – Proper
  9. Excel Text Functions – Trim Function
  10. Excel – Text Function Left Function
  11. Excel Text Function – Right Function
  12. Excel Text Functions – MID Function
  13. Excel Concatenate Text Function
  14. Excel Nested Functions
  15. Excel Search Function
  16. Excel – Calculate Age using Date Function Today
  17. Calculate Tenure in Excel with Days Function
  18. Autofill Dates in Excel
  19. Formatting Dates in Excel
  20. Excel Date Format Shortcut
  21. Calculating Sum of Time in Excel
  22. Calculate Expiry Date in Excel 2016 – EDATE
  23. Find Age or Tenure using DATEDIF in Excel 2016
  24. RandBetween – Generate Random Dates in Excel 2016
  25. Excel Weekday Function
  26. Excel WeekNum and ISOWeekNum Functions
  27. Excel Networkdays and Excel Networkdays.INTL
  28. Excel Workday and Workdays INTL Functions
  30. Calculate Time Difference in Excel
  31. Calculate Quarter in Excel
  32. Rounding Day, Hour and Minutes in Excel
  33. Excel Pay Rate and Downtime Cost Calculation
  34. Excel Logical Functions IF, AND, OR
  35. Calculate Bonus in Excel Using IF Function
  36. Excel Learning – SumIF and SumIFS
  37. How to use Excel Count Functions
  38. How to use VLOOKUP in Excel
  39. How to vLookup in a different workbook in Excel
  40. VLookup for Multiple Sheets with Example
  41. Excel Custom Quarters
  42. Vlookup Return Custom Value

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.

Default image

Full-Time - DevOps Engineer & Part-Time - Blogger

%d bloggers like this: