Formulas: Sumifs, and Index & Match

When creating financial or other data models in excel, formulas as provided by excel functionality can become indefensible in creating an efficient model that is potentially reusable. In a prior internship, I exhausted the use of the (1) 'SUMIFS', (2) 'INDEX', and 'MATCH' formulas (index and match are used in conjunction with eachother).
(Note, this blog is not a tutorial but rather to provide examples for each formula mentioned.

SUMIFS
This formula works great in summing an array of numbers based on defined conditions. An easy example would be if you had financial output that was displayed on a month by month basis. But say that you wanted to summarize this detailed information into quarterly or yearly numbers. Use of this formula does not need to some a set of numbers; it can be used to single out a particular number based on a defined selection criteria. I've heard it referred to as 'poor mans SQL'.

Here's an example of SUMIFS and changing monthly into quarterly data. Cell G6 shows the formula used in cells G3:G6.

INDEX & MATCH
This combination can be used to select data that has a X and Y axis and linearize it. I get the most use of this formula when linearizing government data which is presented in an Year by Month format.
The INDEX syntax is: INDEX(array, row_num, [column_num])

• array is the selection area (not including the columns and row names such as year and month)
• row-num is the row to pull the selection from
• column_num is the column to pull the selection from

You can think of this formula as creating a cross-hair on the exact cell that you want to pull a value from. 

MATCH is used in the row_num and column_num to dynamically select the correct row and column. Otherwise, a number (position) has to be manually input. 

The MATCH syntax is: MATCH(lookup_value, lookup_array, [match_type])

• lookup_value is the value that you want to match in either column or the row, whichever you are targeting in the INDEX formula
• lookup_array is either the column or row names such as the years as the rows or the months (or quarters) as the headers. 

See below for an example:

Hope this helps you think of ways to better your financial models and cut down on time fooling with excel!