SUMIF is a function in Excel which adds all the numbers in a range of cells based on one criteria. It is a worksheet function. It is categorized as a ‘Math/Trig Function’ and is built-in function in Excel. Being a worksheet function, it can be entered as a formula or a part of formula in a cell of a worksheet function.

## Syntax

The syntax of the SUMIF function is –

=SUMIF( range, criteria, [sum_range] )

*range* is a parameter that defines the range of cells that will be evaluated by the ‘*criteria*’ parameter.

*criteria* is a parameter that defines the condition that is to be met in the ‘*range*’ parameter. It can be a number, a logical expression, text, a cell reference, a date or another function.

*sum_range* is an optional parameter that defines the range of cells to sum together. If omitted, this parameter is same as the ‘*range*’ parameter.

## Working

Let’s take a simple example to understand how SUMIF works. Suppose we have a data of 30 numbers. We want to find out the sum of all the numbers whose value is greater than 100 in that data. The numbers are in the cells A1 to A30. The function to find the sum using the SUMIF function will be –

=SUMIF(A1:A30,”>100”)

Here, the range parameter is A1:A30 and the criteria parameter is “>100”. Here the *sum_range* is not specified. Hence, the *sum_range* equals the range which is A1:A30.

Alternatively, we can use cell reference. We put the value ‘100’ in a cell, say, D4. Then we can compute the required sum using this cell. The function for this case will be –

=SUMIF(A1:A30,”>”&D4)

Let’s take another example where the *sum_range* parameter is used. This parameter is used in cases where the range over which the sum is to be calculated is different from the range over which the criteria is to be imposed. For example, we have a data of population of a country and also altitude data for each region of the country.

If we have to find out the population of people living at an altitude of over 1200ft. Then our formula will look something like –

=SUMIF(C2:C79,”>1200”,B2:B79)

Where column C contains population data and column B contains altitude data. Here the *range* parameter is C2:C79 and the *sum_range* parameter is B2:B79

## Wildcard in SUMIF

SUMIF is one of the few functions that can use wildcard characters. Using a combination of sum-range and wildcard characters, we can get useful results.

Let’s take an example to understand this. Imagine a scenario where we have data of students enrolled in various subjects. The subjects are defined by the subject name and teacher.

Supposed we have to find out the total number of students enrolled in Mrs. Mehta’s subjects. Mrs. Mehta teaches maths and English. Other subjects may be history, chemistry, biology, etc.

So we will simply add the count students enrolled in Maths and English. Using SUMIF function will look for this something like –

=SUMIF(A2:A18,”Mehta*”,B2:B18)

In this example, we have teacher name followed by the subject name in cells A2 to A18 (like ‘Mehta Maths’) and in the corresponding B column, we have number of students. The SUMIF function will give the output as the total number of students that Mrs. Mehta will teach.

Note that in this example we use the *sum_range* parameter. The *criteria* is applied on column A but the sum is calculated of column B values. In the previous example, we did not use the *sum_range* because the *criteria* was applied on the range of values.

## Some remarks on SUMIF

- SUMIF cannot return results from a closed external workbook.
- The
*sum_range*must have the same number of rows and columns as that of range. - While computing wildcards, values in
*range*will be evaluated based on criteria applied on*sum_range*.

## SUMIFS – The multiple criteria function

SUMIFS is just like SUMIF except we can put more than one criteria on our range of cells. The syntax of SUMIFS is –

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

Here,

*sum_range* is the cells to sum

*criteria_range1* is the cells to apply criteria1 on

*criteria1* is the criteria to be applied on *criteria_range1*

## Example of using SUMIFS

To understand how SUMIFS works, let’s consider a simple example. Suppose we have an electronic shop’s mobile inventory data. The data gives details of various specs of the phone which include screen size, price, colour, and quantity.

Suppose we have to find the number of phones with screen size exactly 5 inches, price below 25,000 rupees and black colour.Then our formula to find out will look something like –

=SUMIFS(H4:H56,G4:G56,”=5”,F4:F56”<25000”,E4:E56,”Black*”)

Where the H column contains quantity, G contains data of the screen size, F contains data of price and E contains data of colour. Here, the sum of quantity of phones was calculated based on conditions on its screen size, price, and colour by the SUMIFS function.

This is a simple example where multiple criteria are imposed on our data to obtain a specific value. The criteria can be changed according to the requirement of the user. More complex formula can be written according to the complication of the required conditions and the data.

## Conclusion

The basics of SUMIF and SUMIFS has been covered in this article with the help of a few examples. Using these functions actually in Excel will depend on your requirement while working with data. There are many creative ways with which these really useful functions can be used in Excel.

These formulas help to compute sums and are handy in situations where sum of a range of data can give meaningful results given a set of conditions are imposed while calculating the sum.

## Comments are closed.