Sum if in Excel: how to
The sumif formula is built according to a simple principle. Enter a If-area, Equal-area and Then area. For example: If a cell in column A is equal to "tomato", then sum all corresponding values in column B.
- The formula behind this is: "=sumif(range;search criteria;[Sum_range]) ".
- Range: Here you specify the area in which the search criteria are given. When you evaluate, for example, a table in which the transactions in column B are assigned to the individual products (column A), enter in column A as a range.
- Search criterion: The search criterion is the term you want to sum the Numbers to choose. You can either refer to a cell in which you enter the search criteria, or enter the term directly.
- Sum_range: the range in which the values to be summed are listed. In our example, column B.
Sum when to apply: An example
The sumif formula is one of the more complex Excel formulas. The easiest way is with an example explain:
- In our example, a list of individual sales in column B certain products in column A are associated with it.
- In cell E2, a search criterion is specified. In cell E3, and outputs the result. Here we enter the formula.
- In the first line, the Headings (product and sales) are. The ranges start at row 2, in our example, the last value is in row 14.
- The formula is: "=sumif(A2:A14;E2;B2:B14)".
- Excel adds up all sales made by tomatoes. If you want to get the sales of a product, enter the appropriate search criteria in cell E2.
Sum if in Excel apply
Function of the sum if formula
To be able to use the sum if formula correctly, you should be on the way clear.
- Excel works here is matrix-oriented. That is, the program works with specific cells but with areas.
- Excel searches the specified range according to the search criterion. The program remembers the positions where the terms occur in the Form of "column 1, row 2 is" (1,2). It provides no references to the specific cells, but divides the specified area into a new row-column System.
- With the sites, in our example, 1,2; 1,5; 1.10 and 1.14 in Excel is now in the total area and sums the values at the given Points.
- That is, the sum works if only if the range and sum-range have the same number of columns and rows.
- Applied to the example, you could expand the table by inserting both the products, as well as for the sales of additional columns for the individual days of the week.
- Make sure that the two areas must be equal. In the picture you can see a possible breakdown.
Sum if formula in Excel
Tips and Tricks for the sum if formula
- As a search term, you need to specify any cell. You can also type directly into the formula a search parameter. This can be a term or a formula such as, for example,"
YOU MAY ALSO LIKE
0 COMMENTS
LEAVE A COMMENT
RECENT POSTS
The Role of Statistical Arbitrage in Quantitative Finance
Wed 24, 2025
From 'Da' to 'I Do': A Gentleman's Roadmap to Marrying a Ukrainian Woman
Tue 23, 2025
Integrating Cobot Arms with AI: The Future of Smart Manufacturing
Tue 23, 2025
