How is Sumifs function used in Microsoft Excel?

The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

The syntax for the SUMIFS function in Microsoft Excel is:

SUMIF( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

Parameters or Arguments

sum_rangeThe cells to sum.criteria_range1The range of cells that you want to apply criteria1 against.criteria1It is used to determine which cells to add. criteria1 is applied against criteria_range1.criteria_range2, ... criteria_range_nOptional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 127 ranges.criteria2, ... criteria_nOptional. It is used to determine which cells to add. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.

The Excel Sumif function finds the values in a supplied array, that satisfy a given criteria, and returns the sum of the corresponding values in a second supplied array.

Sumifs works just like filter. It comes up with the sum of amounts that we give the specification. for example We have a set of data in which there are daily sales of different fruits. If we set the function such that it sum the total of a particular fruit on a particualr day, then it will add all the sales of that fruit on that day. Some thing special with sumifs is that if we change the selected data then it automatically updates the result.

The SUMIFS function in MS Excel is one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific currency value. 

the following example will elaborate SUMIFS implementation:-

we have here multiple items from different manufacturers in a shop, we want to know how many tables have been sold manufactured by ikea

 

using SUMIFS we can find this easily

 

 

the arguments of SUMIFS function ordered by its presence in the formula :-

1-sum_RANGE which is the range have the numbers  we want excel to sum in our example(column B) (bare in mind SUM_RANGE always have numbers no "strings")

2- criteria_range1:- is the range of cells which contain the criteria associated to the sum range in our example (coulmn A)

3- criteria_1:- is the specific criteria we want to extract from the  from criteria_range1 in our example Table or (cell A3)

same thing applies to criteria_range2 (column C) and criteria_3 Ikea or (cell C5).

The sumifs function =sum a range based on multiple conditions/creteria .

example .

sumifs(sum_range,creteria_range1,"creteria1",creteria_range2,creteria2......)

 

 

 

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

SUMIFS tells Excel to sum a column or row if one or more creteria is fulfilled. As you can see SUMIFS starts will the sum range which represents the column or row that needed to be calculated.

criteria_range1 represnets the range that where formula will look for the creteria that needed to be met. It can be a column or row as well.

criteria1 represents the creteria needed to be met.

You can add creterias as much as you want in formula. For example: 

=SUMIFS(H1:H100,J1:J100,"Refundable",N1:N100,"No")

Here I'm telling Excel to sum the expenses in the range (H1:H100) and make sure the expenses are refundable in the range (J1:100). Also I added one more creteria tells excel to make sure that the expenses are not finalized by Finance Dept. in the range (N1:N100).

So now Excel will sum only the expenses that are refunadble and are not finilzed yet in Finance Dept.

You can use my example in Excel to understand it

 

The syntax for the SUMIFS function in Microsoft Excel is:SUMIF( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

The SUMIF function syntax has the following arguments:

• range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

• criteria   Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.

  Important: Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.

• sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

• You can use the wildcard characters—the question mark (?) and asterisk (*)—as the criteria argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

think of it suming something with a lot of conditions to be true first

In Microsoft Excel, Sumifs function is used for summation or say the addition of all numbers of the cell.