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.
Read on here for more tips on controls and formulas in Excel.
Structure of the SUMIF function
SUMMEWENN(Suchbereich;Suchkriterium;Summe_Bereich)
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. Is that what it says ? 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 SUMMEIFS (Sum_Range; Criterion_Range1; Criterion1; Criterion_Range2; Criterion2; ...) . A similarity can be clearly seen.
Use of the SUMIF function
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" (i.e. 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 added are.
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 150 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.
