Excel SUMIF Function Explained

365 Careers
A free video tutorial from 365 Careers
Creating opportunities for Business & Finance students
4.5 instructor rating • 71 courses • 1,390,196 students

Learn more from the full course

Beginner to Pro in Excel: Financial Modeling and Valuation

Financial Modeling in Excel that would allow you to walk into a job and be a rockstar from day one!

13:05:11 of on-demand video • Updated February 2021

  • Master Microsoft Excel and many of its advanced features
  • Become one of the top Excel users in your team
  • Carry out regular tasks faster than ever before
  • Build P&L statements from a raw data extraction
  • Build Cash Flow statements
  • Discover how to value a company
  • Build Valuation models from scratch
  • Create models with multiple scenarios
  • Design professional and good-looking advanced charts
English [Auto] Hi and welcome back in this exercise will apply the same if functions in practice, I can imagine you already know how some IFES works and now it's time to see how it's applied in practice. Let's open the exercise file. The database sheet contains data for transactions carried out in a chain of supermarkets. We have to use this information to fill in the two tables that are the exercise one and exercise two sheets with an annual breakdown of revenues. The information in database is organized in a table format and consists of more than 15000 rows. This doesn't scare us, right? We know how to deal with large quantities of data. Let's insert a filter has it is much easier to work with. Once we've done that, I'll use the ALZ plus A plus T shortcut in order to carry out the command faster. It's always a good idea to spend an extra minute looking through the material in order to get a better understanding of the data source. Let's go through the source table and describe the type of information that it contains. The first column shows the financial period in which the transactions in each row occurred. The database contains transactions from four periods 2017, 2018, 2019 and 2020. In column C, we can see the type of store in which the transactions were carried out. There are three types of stores convenience stores, hypermarkets and supermarkets. The next column contains a product group classification, meat, coffee, alcohol, etc.. Column E provides information about the producer of the products, while Column F contains an internal code used by the accounting department of the firm in order to classify all items, we are not going to use it in this exercise. In the last four columns, we have data about the number of units that were sold, the unitary cost of the items, the unitary price at which they were sold and the amount of revenue that was registered given by the product of volume and unitary selling price. The only figures that we will need for this exercise are the ones in the last column revenues. Now that we've done our initial screening, we can go ahead and solve the exercises in exercise one, we have to fill in a table with the amount of revenues that each of these product groups had throughout the four financial periods. Some if would not be sufficient for this task. We need to work with multiple conditions, hence some IFES comes into play above each of the financial periods. Let's type the format that was used in our database. By doing this, we will be able to use these cells as criteria. In the sum FS function, we have 2017, 2018, 2019 and six months of 2020. The first arrangement of some IFES is the sum range. This will be column J in the source chih revenues. Let's select the whole column J and fix it. Then we have to pick the range of our first criteria. Given that we have to find revenues of each product group, I suggest that we select the column containing product groups. Column D I am fixing column D as well. Back to our output table. A product group Criterion lies in B five. Let's fix its column reference. Good. This was our first criterion. Now we have to include a second one. The range for our second condition is column B in the database sheet where we have the year in which A given transaction occurred. Then we can go back to the exercise one sheet and select C three as a criterion, which is the respective financial period. Let's fix the row reference of the cell I. I'm doing this because later when we copy the function downwards, we want our criterion to remain in the third row. We will not fix the column reference because we want the criterion to be updated when we paste the function to the right. For example, 2017 will be updated with 2018. If we copy one cell to the right, the sum FS function, he's ready. We can paste it for the whole table by using paste special formulas. Some ifs calculates the amount of revenues that a specific product group had in a given period of time. Moreover, because. He fixed criteria references properly, we are able to fill in the whole table by typing the function only once. Good. Let's move on to the second exercise here. Things get a bit trickier. We have an additional degree of breakdown as we need to take into account the store type as well. It is not difficult to carry out this task. The only thing that we need to do is to include an additional element in the some IFES function. We will have the same thing as before, plus an additional condition store type. Above each of the columns, I will type the financial period that it refers to. We can easily use these cells as criteria of the same AIFS function. Let's type some ETFs and select as a first argument, the column in the database sheet containing the amount of revenues we have to fix its references because later we will copy the function to the right. Then we want to insert the conditions that have to be respected. The first two conditions are identical to the ones we had in exercise. One, by introducing the two conditions related to the product group and the financial period, some FS will add only the rows that respect these conditions in order for some FS to sum the revenues in J, it has to verify that the year and product group conditions are satisfied. We need to include a third condition as well, though, namely type of store. I'll select the column containing information about the store type and pick C for in our output sheet. By doing this, we are assuring that only revenues from meat products in convenience stores registered in 2017 are summed by our function. Please note that similarly to what we did for the financial period criterion, we will fix the reference of the store type criterion as we would like to be able to maintain the fourth row as criterion when we paste the function downwards. How some FS function with three conditions, he's ready. Let's paste the function to the entire table. As you can see, fixing reference is properly allowed us to type the formula only once and then copy it for all periods. Our output table is now ready. It provides three degrees of breakdown, product group type of store and financial period. This is how you can use some FS in order to create sophisticated and dynamic formulas in Excel. The core focus of this exercise is twofold understanding how to work with some IFES and practicing fixing cell references properly in order to be as efficient as possible. Stay tuned and thanks for watching.