Excel yourself at layout

This solution formula combines advanced techniques to summarise values from a reasonably common layout.

QUESTION

I have a layout that is hard to extract from. Could I use a SUMIFS function to summarise either the Actual or the Budget columns for each month to arrive at a full-year forecast?

ANSWER 

The layout you refer to, shown in Figure 1, is reasonably common. I will use the SUMIFS function in combination with the INDEX, MATCH and IF functions to solve this problem. This technique can be used when you need a flexible way to refer to a column within a range. Act = Actuals and Bud = Budget. The Figure 1 layout is in a sheet called Data.

Figure 2 shows the report layout we want to populate. As the year progresses the Bud entries in row 1 are manually changed to Act. The report must automatically update and extract from the relevant columns in Figure 1.

Figure 1The solution uses a number of advanced techniques. Normally the INDEX-MATCH combination is used to extract single values. Here I’ll share a technique where the INDEX function returns a column of entries. We covered the SUMIFS function in April 2017 INTHEBLACK, so I’ll focus on the INDEX-MATCH combination.

In the Figure 2 report, as we copy our report formula across, we need a flexible way to identify the correct column to add up in Figure 1, either Act or Bud, depending on the entry in row 1 of Figure 2.

INDEX function

The INDEX function has a special syntax to return a column, not a cell. 

Special syntax (column selection): 
INDEX(Range,, Column_Number)

The example below is based on Figure 1. The INDEX function below refers to the range E3:E12: 

INDEX(Data!$C$3:$N$12,,3)

The INDEX function usually has three arguments but we have only used two: the range to extract from and a column number to extract from that range. Note the two commas in front of the 3. Omitting the second argument instructs Excel to return a column from the range.

Professional Development: Manipulating formulas and using forms in Excel 2010: This course discusses various statistical functions, including those designed to work with averages, determine the median, pinpoint rank, percentages, and sample populations.

The 3 on the end of the INDEX function tells Excel to return the third column from the range Data!$C$3:$N$12, which is E3:E12. 

You can’t use this formula on its own because it is returning a range. You must use the previous INDEX formula with a function that works with a range, such as the SUM function. The formula below adds up the range E3:E12 in the Data sheet:

=SUM(INDEX(Data!$C$3:$N$12,,3))

Check total

Row 9 in Figure 2 has a check total. This formula needs to add up the correct column from Figure 1, based on whether we require actuals or budget for that month. We can adapt the previous formula to use the same INDEX function we will use in the final solution formula. It’s easier to understand when used in isolation with the SUM function. 

The formula for our check row in cell B9 in Figure 2 is:

=SUM(INDEX(Data!$C$3:$N$12,,MATCH(B$2,Data!$C$1:$N$1,0)+IF(B$1="Bud",1,0)))

This formula can be copied across. The INDEX function provides the whole range for the SUM function. This will confirm the summarised values in row 3 to 7 in Figure 2 are correct. Let’s review the INDEX function: 

INDEX(Data!$C$3:$N$12,,MATCH(B$2,Data!$C$1:$N$1,0)+IF(B$1="Bud",1,0))

We saw that the INDEX function can return a range, but the example had the 3 keyed in. We have replaced the 3 with a MATCH function and an IF function. This provides the flexibility we need.

MATCH function

Syntax:
MATCH(Lookup_value, Range, Lookup Type)

The MATCH function returns a number that represents where an entry is located within a range. This number will identify the correct column number within the Data!$C$3:$N$12 (Figure 1) for the month we want to add up. Let’s review the MATCH function in isolation:

MATCH(B$2,Data!$C$1:$N$1,0)

Figure 2Cell B2 in Figure 2 contains the month we want to extract, Jul 17. The MATCH function looks for Jul 17 in the range C1:N1 in the Data sheet. 

It is vital the columns used in the MATCH range C to N line up perfectly with the columns referred to in the INDEX range $C$3:$N$12. The 0 at the end of the MATCH function is the Lookup_Type and it means to use an exact match; if the date isn’t in the range, an error will be returned.

This works perfectly for all actual months, as the Months in Figure 1 are directly above the Actual columns. The problem arises for the Budget months. 

We need a way to adjust the MATCH result so it selects the next column to the right of the Actual column, which is the Budget column.  

IF function

An IF function can add 1 to the MATCH result for Budget months only, otherwise 0 is added. If row 1 contains Bud, the MATCH result is adjusted one column to the right, which is the Budget column for the month:

+IF(B$1="Bud",1,0)

Solution formula

Our solution formula will use a SUMIFS function with the syntax below.

Syntax:
SUMIFS(Sum_Range, Criteria_Range1,Criteria1)

Below is the formula for cell B3 in Figure 2:

=SUMIFS(INDEX (Data!$C$3:$N$12,,MATCH(B$2,Data!$C$1:$N$1,0)+IF(B$1="Bud",1,0)),Data!$B$3:$B$12,$A3)

This solution formula can be copied across and down.

The Sum_Range is provided by the same INDEX function we dissected previously in the SUM function in cell B9 of Figure 2. 

The Criteria_Range1 is Data!$B$3:$B$12 (Figure 1). It doesn’t change as the formula is copied across, as it lists the States. 

Criteria1 is cell $A3. Column A doesn’t change as we copy it across, so it has the $ sign in front. 

These two arguments are instructing Excel to look in the range B3:B12 in the Data sheet and identify all the WA entries. 

Once WA entries are identified in B3:B12, the corresponding row in the Sum_Range (generated by the INDEX function) is added up. 

As mentioned, this solution formula combines a number of advanced techniques. The companion video and Excel file will go into more detail to demonstrate these techniques.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to a4@iinet.net.au


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

July 2017
July 2017

Read the July issue

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENTS