# 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.

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

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.