In this tutorial, you’ll learn how to write a flexible Excel formula that lets you summarize quarterly or monthly data in an annual format using the INDIRECT, MATCH, and SUMIFS functions. This is a common task given in Excel tests and case studies, especially in industries such as real estate. http://breakingintowallstreet.com/
“Financial Modeling Training And Career Resources For Aspiring Investment Bankers” Table of Contents: 2:54 Using SUMIFS to Make the Dates Flexible 4:59 Using MATCH and INDIRECT to Make the Entire Function Flexible 11:20 Testing the Formula 13:42 Recap and Summary Resources: https://youtube-breakingintowallstree… https://youtube-breakingintowallstree… https://youtube-breakingintowallstree… Lesson Outline:
Some of the most frequently-tested topics in Excel tests include the proper uses of lookup functions (HLOOKUP and VLOOKUP), INDEX/MATCH, INDIRECT, and the SUM, SUMIF, and SUMIFS functions to find and summarize data. Often, interviewers will ask you to write a single function that accomplishes a task elegantly rather than having to modify the function slightly or otherwise change it each time you use it. In many cases, you could write simple SUM formulas to sum up cells manually, but it’s far more robust to use the SUMIFS function so that you can check the dates and include only the matching quarterly or monthly data for the year you’re in. But to make the function truly flexible so that you can copy and paste it down and around and use it to sum up data for different rows, you must use the MATCH and INDIRECT functions. MATCH lets you move down to the appropriate row based on the data you need – for example, if “Profits” is 25 rows down in the monthly spreadsheet, the MATCH function will retrieve 25 when you use it in that spreadsheet with “Profits” as the input. Then, INDIRECT lets you create your own variable references to other spreadsheets. For example, instead of using E9:T9 as the fixed range, you could let the “9” parts vary based on the row or column you’re in or the output of functions. We used INDIRECT and MATCH to rewrite the SUMIFS function with a fixed summation range and make the summation range variable. The function is more flexible because when we copy it down, the summation range reference will change, and the row will match the correct row number of the data we’re seeking.
