How to Use Array Formulas to Report Groups of Accounts

Often, in accounting, marketing, and other departments, we’d like to report and analyze groups of items rather than one item at a time.

In Part 1 of this series, I explained the general strategy for doing this in Excel. In Part 2, I explained how to set up a table of named lists of accounts, where each named list represents an Account Group.

In this lesson, you’ll learn how to use Account Groups in formulas. To do so, we’ll begin by working with this simplified dataset:

You can download a zip file with this workbook, TestAccountGroups.xlsx, at the link. The zip file also includes the other two workbooks I’ve set up for this series.

In this test workbook, I’ve used nine steps to work through the development of the formula we’ll use. We’ll take those steps one at a time…

Account Groups, Test 1

Understanding this Excel feature is critical to understanding the formulas that follow: If we pass an array of values to any of a function’s arguments that are expecting a single value, Excel returns an array of results—one for each value in the array.

In this article, we’ll use this feature extensively with Excel’s SUMIFS function, which has this syntax…

=SUMIFS( sum_range, criteria_range, criteria, … )

…where…

• sum_range is the range you want to sum.
• criteria_range is the first range that you want to filter.
• criteria is the single-value criteria for the first filter.
• … indicates optional pairs of other criteria_range and criteria instructions.

So, if we use this data…

…with the array constant shown in the last argument of this formula…

P3: =SUMIFS( Data[Value], Data[Item], {“Hats”,”Coats”} )

…we’ll get these results in the Dynamic Array (DA) versions of Excel. The actual formula is in cell P3. But because the formula returns two results—one for Hats and the other for Coats—the formula “spills” the results to cell Q3.

With the CSE (Ctrl + Shift + Enter) version of Excel, you’ll first need to array-enter this formula, which will give you a #VALUE! error. This is because Excel is trying and failing to display two values in one cell.

This is a bad outcome for CSE versions of Excel. It also can be a bad outcome for the DA version when we want the sum, not the individual values. So let’s try another test…

Account Groups, Test 2

Because we’re accustomed to using SUMIFS to return our sums, it’s seems strange to use this next test. But it makes perfect sense if you glance again at the previous figure…we need to surround the SUMIFS calculation with a SUM, like this…

P4:  =SUM( SUMIFS( Data[Value], Data[Item], {“Hats”,”Coats”} ) )

…which returns the number 80, which is the sum of the sales of Hats and Coats in the Data table below.

Account Groups, Test 3

The previous formula for cell P4 gives us the results we want for the sales of Hats and Coats. But we don’t want to use those individual items in our formulas. Instead, we want to use Account Groups, as we defined here, starting in column H:

So, because Excel treats our range names as an array of values, we can replace the array constant with the name of a group, like this:

P5:  =SUM( SUMIFS( Data[Value], Data[Item], ag.Clothing ) )

And this version returns 80, as we hoped it would.

Account Groups, Test 4

The previous test was a big step. But for convenience, we often want to specify an account group by entering its name in a cell, as shown here, in cell O6.

This turns out to be simple to do. All we must do is to wrap the cell reference to that label with an INDIRECT function, like this:

P6:  =SUM( SUMIFS(Data[Value], Data[Item], INDIRECT(O6) ) )

But we still have farther to go…

Account Groups, Test 5

Ideally using this data…

…we’d like to be able to specify either an account group or a single item. This is particularly important with financial statements which often include both single and groups of accounts.

However, if we enter Pills in cell O7, and then use this formula…

P7:  =SUM( SUMIFS(Data[Value], Data[Item], INDIRECT(O7) ) )

…we get an answer of zero, because INDIRECT(“Pills”) gives us a #REF! error, indicating that Pills isn’t a range name. And then, SUMIFS returns zero because no Item in the Data table is named #REF!.

Account Groups, Test 6

On the other hand, if we remove the INDIRECT function, and enter Pills in cell O8, this formula…

P8:  =SUM(
SUMIFS(Data[Value], Data[Item], O8 ) )

…gives us the value for Pills with no problem at all.

Account Groups, Test 7

And just to confirm, if we enter ag.Clothing in cell O9, this formula…

P9:  =SUM( SUMIFS( Data[Value], Data[Item], O9) )

…returns zero, because no items in the Data table are named ag.Clothing.

Account Groups, Tests 8 & 9

And therefore, to get around this problem, we need to change the criteria range to test for that #REF! error, like this:

P10: =SUM( SUMIFS( Data[Value], Data[Item], IF(LEFT(O10,3)=”ag.”,INDIRECT(O10),O10)))

That is, if the first three letters in the label in cell O10 begin with “ag.” the formula returns the array returned by INDIRECT(O10), otherwise, it returns a reference to cell O10.

And if we copy that formula to cell P11, we get the sum we expected for the Account Group AB.Clothing.

The Test Report

Now that we’ve worked out the correct format for our SUMIFS formulas, we can set up a simple report like this.

If you have the CSE version of Excel, you would use this formula for the first item name:

P16: =INDEX(Meta[Name], MATCH(O16,Meta[Item], 0))

And you’d array-enter this formula…

Q16: =SUM( SUMIFS( Data[Value], Data[Item], IF(LEFT(O16,3)=”ag.”,INDIRECT(O16),P16)))

…and then copy it to cells Q17 and Q18.

And if you use the DA (Dynamic Array) version, you could use the XLOOKUP function to return the item name:

P16:  =XLOOKUP( O16, Meta[Item], Meta[Name] )

And you would enter the formula above for cell Q16 normally.

Setting Up the Income Statement

This Income Statement I showed you in Parts 1 and 2 of this series uses the same formula pattern that we used for the Item Summary above…

…with two exceptions.

First, in the test formulas, we used the constants “ag.” and 3. However, it’s best practice to name those constants and then use the names of the constants in your formulas.

One reason this is a good idea is that this method allows us to document what the constant represents. We don’t want magic numbers or text in our formulas.

Another reason is that if we ever want to change a constant for some reason, we can do it in one or two cells. Otherwise, we’d have to change many formulas.

It’s best practice to name those constants in a Control sheet, as shown here. A Control sheet also typically contains settings and other categories of workbook-scoped range names.

Here, “cg.” is the prefix for named cells that have a workbook-global scope.

The second exception is that it’s not enough to return the total for accounting data, we also must be clear about its sign.

The easiest way I’ve found to manage signs in Excel is to multiply all reported numbers from a General Ledger by their natural sign. When we do so, all debits and credits turn positive if they have the same sign as their natural sign; and they turn negative when they have the opposite sign of their natural sign.

So, for example, suppose a sales account contains a very large refund, one so large that sales has a debit balance for the period. When we multiply that (debit) balance by the natural sign for a sales account (-1), we would correctly report sales as a negative value for the period.

So here’s the formula for the first sales account in the Income Statement shown below:

F3: =SUM( SUMIFS( Data[Amt], Data[Acct], IF(LEFT( $A3, cg.PrfxLen ) = cg.PrfxAGs, INDIRECT( $A3 ), $A3 ), Data[Date], F$2 ) ) * $B3

Of course, if your source data doesn’t use the Debits-are-positive-and-Credits-are-negative convention, then you wouldn’t multiply the formula in cell F3 by a Sign value.

If you have the CSE version of Excel, you would use this formula to return the label shown in cell E3:

E3:  =INDEX(Meta[Name], MATCH(A3,Meta[Acct],0) )

And if you have the Dynamic Array (DA) version, you could use this formula instead:

E3: =XLOOKUP( $A3, Meta[Acct], Meta[Name] )

But how do you know that these formulas are correct? That’s what we’ll discuss in the last article in this series.

If you haven’t already done so, you can download the workbooks for Account Groups at this link.

Here are the names of all four articles in this series, with their links if they’ve been published:

Part 1: How to Report GL Account Groups in Excel
Part 2: How to Define General Ledger Account Groups in Excel
Part 3: How to Use Array Formulas to Report Groups of Accounts
Part 4: How to Set Up an Automatic Error-Checking System in Excel Reports

Also, if you haven’t already done so, please support my Excel.UserVoice requests about the INDIRECT function and the array of arrays—as I described in Part 2, How to Define General Ledger Account Groups in Excel.

Do you know of other Excel users who could find this article useful? Now would be a great time to send them a link to it or to post it to a social group.