Using multiple criteria in SUMIF Function

Source:  http://www.exceltip.com/summing/using-multiple-criteria-in-sumif-function.html

In this article, we will learn how to use the multiple criteria in Sumif and Sumifs function.

Problem is: – Summing the total from cells in one column that meets the criteria based on a range of dates in another column.

This problem we can resolve through 2 Excel formulas:-

1)    SUMIF function: -This function is used to sum up the cells on the basis of multiple criteria.

Syntax of SUMIF:-=SUMIF(range, criteria,[sum_range])

Looks like you could use multiplication sign(*) and plus sign(+) in the criteria to include multiple values if you have for specific column.

As sum+ If function also works similar to SUMIF function.

=SUM(IF(A1:A10>=1,IF(A1:A10<=10,1,0)))

=SUM(IF((A1:A10>=1)*(A1:A10<=10),1,0))
=SUM(IF((A1:A10>=DATEVALUE("1/10/99"))*(A1:A10<=DATEVALUE("2/10/99")),1,0))

=SUM(IF((A1:A10>=1)*(A1:A10<=10),B1:B10,0))
=SUM(IF((Test1)+(Test2)+...+(Testn),1,0))

Syntax	Range	Criteria	Sum_Range
=SUMIF(range, criteria,[sum_range])	Data range from which we want to retrieve the sum	For which we want to calculate the sum from the data	The range of column from which we want calculate the sum

2) According to Microsoft Excel SUMIFS can be defined as a function that “Add the cells specified by a given set of conditions or criteria”.

The syntax of SUMIFS function is as follows:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Here, ‘sum_range’ specifies the cells that are to be added based on the given conditions. It is a required field.

‘criteria_range1’ specifies the first range where the first criteria is to be evaluated.

‘criteria1’ specifies the condition that is to be evaluated in the ‘criteria_range1’.

‘criteria_range2’, ‘criteria2’ specifies the other ranges and their respective conditions. It is an optional argument. SUMIFS supports a total of 127 range criteria pairs.