SUMIFS Functions in Excel

SUMIF function is used to add the cells in a range which meets the given criteria. In SUMIF function you can give only one criteria. SUMIFS function adds the cells in a range which meets multiple criteria.

Syntax for SUMIFS function is
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The sequence of arguments are different in SUMIFS function as compare to SUMIF.
sum_range argument is the first argument in the SUMIFS function. This is the range of cells the value of which is to be added based on the given criteria(s).

criteria_range1  the first range in which the criteria1 is the be checked.

criteria1 is the condition to be checked in criteria_range1, which can be text, number, cell reference, formula etc. The criteria with text, logical or mathematical symbol should be enclosed in double quotation marks (“). The wildcard characters, question mark (?) and asterisk (*) can also be used in the criteria.

criteria_range2, criteria2 (Optional) additional ranges and the criteria to be checked. In SUMIFS function we can give up to 127 range/ criteria in a formula.

In this example the data table show the list of candidates applied for a particular job and application fees paid by them.
The eligibility criteria to accept the application is age should be between 18 to 30 years.
To add the fees collected by eligible candidates we can use the SUMIFS function, the formula should check two conditions, the age should be more than or equal to 18 years and age should be less than or equal to 30 years. If these conditions satisfied it will add the fees paid by the candidate.

In the same example, the following criteria can be given to check the total fees paid by the eligible male candidates, to calculate this it should check both age and the gender.

Add a Comment

Your email address will not be published. Required fields are marked *