Excel: Simply apply sum if
With the formula Summewenn in Excel you can add the values from a list that correspond to a certain search criterion. To do this, you have to correctly define several areas. We show you how to use Summewenn correctly in Excel.
Summewenn in Excel: Here's how
The Summewenn formula is based on a simple principle. You specify an if area, an equal area and a then area. For example: If a cell in column A equals "tomatoes", then sum all the corresponding values in column B.
- The formula behind it is: "= SUMIF (range; search criterion; [sum_range])".
- Area: Here you specify the area in which the search criteria are given. For example, if you are evaluating a table with sales in column B that are assigned to the individual products (column A), then specify column A as a range.
- Search criterion: The search criterion is the term by which you want to search for the numbers to be summed. You can either refer to a cell in which you enter the search criteria, or you can enter the term directly.
- Sum_range: Here you specify the range in which the values to be summed are entered. In our example column B.
Apply sum if: An example
The Summewenn formula is one of the more complicated Excel formulas. The easiest way to explain it is with an example:
- In our example, there is a list in which individual sales in column B are assigned to certain products in column A.
- A search criterion is specified in cell E2. The result is output in cell E3. We also enter the formula here.
- The first line contains the headings (product and sales). The ranges therefore start at line 2, in our example the last value is at line 14.
- The formula is now: "= SUMIF (A2: A14; E2; B2: B14)".
- Excel now totals all sales made from tomatoes. If you want to get sales from another product, enter the appropriate search criteria in cell E2.
How the Summewenn formula works
In order to use the Summewenn formula correctly, you should be clear about how it works.
- Excel works matrix-oriented here. That means the program does not work with specific cells, but with areas.
- Excel searches the specified area for the search criteria. The program then notes the positions at which the terms appear in the form "Column 1, Line 2" (1, 2). It does not make any references to the specific cells, but divides the specified area into a new row-column system.
- With the localities, in our example 1, 2; 1.5; 1.10 and 1.14 Excel now goes into the sum area and sums the values at the given places.
- This means that Sumennenn only works if the area and the sum area have the same number of columns and rows.
- Applied to the example, you could expand the table by introducing additional columns for the individual days of the week for both the products and the sales.
- Make sure that the two areas are the same size. In the picture you can see a possible division.
Tips and tricks for the Summewenn formula
- You do not have to enter a cell as a search term. You can also enter a search parameter directly in the formula. This can be a term or a formula such as "<1" (enter with quotation marks).
- You can select the same range as the range and total range. For example, you can add all the numbers in a range that are less than 1. (see image)
- Instead of entering the areas manually (A1: A45), you can also select them with the mouse. Click in the first cell of the area and drag the selection to the last cell.
- If you have a particularly large area, you can also select it as follows: Click in the first cell and hold down [Ctrl] + [Shift]. You can now use the arrow keys to select a contiguous area to the right and down (one after the other).
- You can also use multiple search criteria and areas. Use the formula "SUMIFS" for this. This allows you to include several areas and criteria in a formula. This works exactly as described above, after the first area_sum start again from the beginning and enter any number of other areas.
