How to use SUMIFS function in MS Excel?

In mathematics or any calculation, addition is the basic function or calculation, but sometimes we need to add some data with multiple conditions or criteria.

To do the conditional addition in MS excel there is a method for this task and this is very easy as well.

So let’s start doing SUMIFS in MS Excel.

What is SUMIFS function: This function adds the cells specified by a given condition or criteria.

Difference in SUMIF and SUMIFS is you can apply more than one criteria.

  

Open your MS excel file, put some data into it. For you, I have collected some data in the file as shown in below picture.

In first column (A) I have ‘S.No’, in second column (B) there is list of ‘Category’, in third (C); ‘Food’, fourth (D): ‘Salesman’ and in Fifth (E) there is ‘Sales’.

 

 

Here we want to find the sales done by the salespersons. If I want salesperson’s sale of Fruits and Vegetable separately then it should be calculated automatically.

In this example, I will calculate for Sonu’s sales for Fruits and Vegetables.

 

  Type ‘=SUMIFS’ in Cell E16 beside the Cell D16 (Sonu-Fruits), press the ‘tab’ key, function will be selected.

  

Now it is asking for

‘sum_range’, ‘criteria_range1’, and ‘criteria1’, and ‘criteria_range2’, and ‘criteria2’ so on.

sum_range: this is the range, which has to be summed, numerical value which are to be added.

I select the range from E3 to E14, since sales data is in column ‘E’.

criteria_range1 : select the range ( in column) with which you want to match the criteria1.

For example, in our case we want the total sales for category (Fruits & vegetables) by the salesmen, so we will select range from ‘Category’ (Column B).

Therefore, I select range from B3 to B14 and type ‘,’.

 

Now next is ‘criteria1’.

criteria1 : It refers to the first criteria for which SUM has to be done, this is the condition, in fact.

For this, I type “Fruits”. Since we want sales for Fruits only.

criteria_range2 : select the range ( in column) with which you want to match the criteria2.

For example, in our case we want the total sales by the salesmen, Sonu, so we will select range from ‘Salesman’ (Column D). So I select range from D3 to D14.

 

Now next is ‘criteria2’.

criteria2 : It refers to the second criteria for which SUM has to be done.

For this, I press ‘,’ then type “Sonu”, close the bracket and hit the ‘Enter’ key.

 

So, now we have the data of sales by Sonu, he sold Fruits of Rs. 1600 (800 + 800 = 1600).

Now I calculate the sales of Vegetables by Sonu.

Copy the formula; do not copy the cell, copy the text only and paste in cell E17 beside cell D17 (‘Sonu-Vegetable’) like below picture.

 

Change the “Fruits” into “Vegetables” and hit the ‘Enter’ key, it will sum the sales of Sonu for Vegetables.

  

You have the total sales of Sonu for vegetables only.

Sonu’s Total Sales for Vegetables = 1000 + 1000 = 2000.

 

So in the same manner you can make addition with various criteria or conditions.

So this all was about adding data for multiple specific criteria using SUMIFS function in MS Excel.