Understanding the Sumif Function in Excel

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.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!