Clear easy guide on how to use SUMIF in Excel explains structure of SUMIF and gives illustrated examples of its use in different ways.
Find more education guides, tips and advice
Find more business guides, tips and advice
How to find circular references in Excel: step by step
How to freeze columns in Excel: easy step by step guide
Your business or research education life can be easier and more practical when you use the right Microsoft Excel worksheet and the power of its tools. We have also already published some steps to make your tweets private. This time we’re going to talk about the importance of SUMIF to better manage every area of your business.
This function is used to adjust the sum of the values based on criteria that you can select. In reading this guide you will learn to use the SUMIF Excel function for any business situation and can produce a worksheet with feedback on the SUMIF function.
The SUMIF function in Excel is very useful for specific addition. We’ll show you how to use it properly.
In Microsoft Excel you can use the so-called SUMIF command. This is there to add specific values. The values in the previously defined area are only added to the total if they correspond to a defined search criterion. In the following we explain the use of the SUMIF function with a few examples.
- Structure of the SUMIF function
- Use of the SUMIF function
Structure of the SUMIF function
SUMIF(Search Area;Search Criterion;Sum_Range)
Search area: The cells in this area are used to search for the search criterion. The search area can contain all possible attributes. It doesn’t matter whether it’s numbers or names, for example. Empty cells are ignored.
Search criterion: Here you can enter your search attribute. If your criterion contains a numeric attribute or a mathematical operator, it must be enclosed in quotation marks. Search criteria can be, for example: “Profit”, “> 10”, 1 or TODAY ().
You can also replace parts of the criterion with a question mark or an asterisk. Here is the ? for one character, while the * can replace any character string. So instead of “Profit” you can still search for “G? Winn”.
Sum_Range: This part of the function is optional. You only have to specify it if the actually totaled values are in a different range than the criterion. In the examples above, this is the case with “Profit” or TODAY (). After all, these are not numbers that can be summed up, just the reference words.
By the way: If you want to use several search criteria at the same time, you have to use the SUMIFS function . This is structured according to the scheme SUMIF (Sum_Range; Criterion_Range1; Criterion1; Criterion_Range2; Criterion2; …) . A similarity can be clearly seen.
How to use SUMIF function in Excel
In the following we would like to illustrate the use of the SUMIF function with two examples. In the first example, numbers according to the criterion “> 10” (ie numbers greater than 10) are to be added up using the command. The second example lists the profits and losses of a fictitious company. Here now are all profits are added.
Example 1
In this example, the formula SUMIF (A1: A10; “> 10”) was used. A1: A10 was specified as the search area . The colon means that the range goes from cell A1 to cell A10, i.e. along the A column. “> 10” was specified as the criterion . The quotation marks are necessary because of the larger character. The criterion means that all numbers greater than 10 should be added up. You can see that the sum_range has been omitted, because here the summed range corresponds to the search range.
The sum of 150n is made up of the numbers in A4, A5 and A6. Line A10 is ignored because the number in it is equal to 10 and not greater than 10. If you want to make such an entry and include 10, you must write “> = 10” .
Example 2
The formula SUMIF (A1: A10; “Profit”; B1: B10) is used here. The search area is defined as A1: A10 . “Profit” was used as the search criterion here . In this example, too, quotation marks must be used, as the word profit is not a number.
In addition, a sum_range has now also been specified. While the definition is in the left column, the actual value is given in the right column. So cells B1: B10 were given as the sum area.
The result 37600 is the summation of rows 1, 4, 5, 6 and 10.
