Sign in
  • Blog
  • All Posts
  • Business & Economics
    • Accounting & Finance
    • Analytical Charting
    • Analytical Methods
    • Business Statistics
    • Cash Flow
    • Data Management
    • Data Visualization
    • Economic Indicators
    • Financial Analysis
    • Forecasting & Simulation
    • Reporting
    • Tests & Validations
    • Time Value of Money
  • Excel Methods
    • Charting Methods
    • Conditional Formatting
    • Data Management
    • Error Reduction
    • Excel Dashboards
    • Excel Tables
    • Excel’s Graphic Tools
    • Printing
    • Report Tables
    • Shortcuts
    • VBA
    • Worksheet Interaction
  • Formulas & Functions
    • Aggregation Functions
    • Array Formulas
    • Counting Functions
    • Date-Time Functions
    • Dates and Times
    • Dynamic Array Formulas
    • Financial Functions
    • Lookup Formulas and Functions
    • Range Names
    • Rounding Functions
    • Statistical Functions
    • Text Functions
  • Excel Strategies
    • Excel Productivity
    • Excel-Friendly OLAP
  • Training & Services
  • Downloads
Sign in
Welcome!Log into your account
Forgot your password?
Password recovery
Recover your password
Search
Saturday, February 27, 2021
  • Sign in / Join
  • Contact
Sign in
Welcome! Log into your account
Forgot your password? Get help
Password recovery
Recover your password
A password will be e-mailed to you.
Microsoft Excel User, Microsoft Excel Learning, Microsoft Excel Training, Microsoft Excel Lessons, Microsoft Excel Tips Microsoft Excel User, Microsoft Excel Learning, Microsoft Excel Training, Microsoft Excel Lessons, Microsoft Excel Tips
  • Blog
  • All Posts
  • Business & Economics
    • AllAccounting & FinanceAnalytical ChartingAnalytical MethodsBusiness StatisticsCash FlowData ManagementData VisualizationEconomic IndicatorsFinancial AnalysisForecasting & SimulationReportingTests & ValidationsTime Value of Money
      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.
      Accounting & Finance

      How to Set Up an Automatic Error-Checking System in Excel Reports

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      How to aggregate named groups of GL accounts.
      Accounting & Finance

      How to Report GL Account Groups in Excel

      In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.
      Accounting & Finance

      How to Use Array Formulas to Report Groups of Accounts

  • Excel Methods
    • AllCharting MethodsConditional FormattingData ManagementError ReductionExcel DashboardsExcel TablesExcel’s Graphic ToolsPrintingReport TablesShortcutsVBAWorksheet Interaction
      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.
      Accounting & Finance

      How to Set Up an Automatic Error-Checking System in Excel Reports

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      All Excel reports perform at least four tasks. Good reports assign these tasks to four sections, with specific worksheets defining each section.
      Excel Methods

      How to Structure Your Report Workbook

      Excel's Linked Picture feature (Camera tool) allows us to position a table in reports while ignoring the row and column settings in the report worksheet.
      Excel’s Graphic Tools

      Ideas about Using Excel’s Linked-Picture (Camera) Tool

  • Formulas & Functions
    • AllAggregation FunctionsArray FormulasCounting FunctionsDate-Time FunctionsDates and TimesDynamic Array FormulasFinancial FunctionsLookup Formulas and FunctionsRange NamesRounding FunctionsStatistical FunctionsText Functions
      The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.
      Array Formulas

      How to Use Excel’s LET Function

      Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.
      Downloads

      How to Stack and Shelve Dynamic Arrays

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      We compare Excel's five columnar lookup functions.
      Downloads

      XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

  • Excel Strategies
    • AllExcel ProductivityExcel-Friendly OLAP
      How to aggregate named groups of GL accounts.
      Accounting & Finance

      How to Report GL Account Groups in Excel

      In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.
      Accounting & Finance

      How to Use Array Formulas to Report Groups of Accounts

      If you want all your Excel reports, analyses, forecasts, and other Excel work to be highly productive, this is the only strategy that will work for you.
      Business & Economics

      An Introduction to Excel Data Plumbing

      All Excel reports perform at least four tasks. Good reports assign these tasks to four sections, with specific worksheets defining each section.
      Excel Methods

      How to Structure Your Report Workbook

  • Training & Services
    • Use this Excel dashboard to track 27 economic indicators of the United States' recovery from the Covid-19 recession.
      Training & Services

      Learn How to Use Excel to Track the US Recovery from…

      This Excel dashboard report lets you follow key economic trends while showing you how to gain a HUGE increase in your Excel productivity at work.
      Training & Services

      Track Key Economic Indicators Every Weekday with this Excel Dashboard

  • Downloads
Microsoft Excel User, Microsoft Excel Learning, Microsoft Excel Training, Microsoft Excel Lessons, Microsoft Excel Tips ExcelUser.com
Home Business & Economics Accounting & Finance How to Use Array Formulas to Report Groups of Accounts
  • Business & Economics
  • Accounting & Finance
  • Downloads
  • Formulas & Functions
  • Dynamic Array Formulas
  • Excel Strategies
  • Excel Productivity
  • Reporting

How to Use Array Formulas to Report Groups of Accounts

In Excel, you can report named groups of GL account numbers, product codes, and so on—just as you report one such number. Here’s how.

By
Charley Kyd
1800
Facebook
Twitter
Pinterest
Linkedin
    The formulas in this Income Statement use named Account Groups to sum any number of accounts in one formula.
    The formulas in this Income Statement use named Account Groups to sum any number of accounts in one

    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:

    We'll use these simplified table to test various ways to summarize by groups of accounts.
    We’ll use these simplified table to test various ways to summarize by groups of accounts.

    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…

    We'll use these simplified table to test various ways to summarize by groups of accounts.

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

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

    The result of step 1 in creating account groups for Excel.…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:

    We'll use these simplified table to test various ways to summarize by groups of accounts.

    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 formula in cell P6 uses the INDIRECT function to reference the account group ag.Clothing.
    The formula in cell P6 uses the INDIRECT function to reference the account group ag.Clothing.

    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'll use these simplified table to test various ways to summarize by groups of accounts.

    The array formula tests continued.…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

    The array formula tests continued.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.

    We'll use these simplified table to test various ways to summarize by groups of accounts.

    Account Groups, Tests 8 & 9

    The array formula tests continued.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

    This report relies on a SUMIFS formula to return data for either one account or for an Account Group.
    This report relies on a SUMIFS formula to return data for either one account or for an Account Group.

    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

    The formulas in this Income Statement use named Account Groups to sum any number of accounts in one formula.
    The formulas in this Income Statement use named Account Groups to sum any number of accounts in one form

    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.

    One of the uses of a Control Sheet is it provides a place to name our constants.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.

    Formulas in each row of the Excel Income Statement reference the name of its group account in column A.
    Formulas in each row of the Excel Income Statement reference the name of its group account in column A.

    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.

    • TAGS
    • Account Groups
    • Array Constants
    • Array Formulas
    • Arrays
    • Download
    • Dynamic Arrays
    • Income Statements
    • INDIRECT Function
    • SUMIFS Function
    • XLOOKUP Function
    Facebook
    Twitter
    Pinterest
    Linkedin
      Previous articleAn Introduction to Excel Data Plumbing
      Next articleHow to Report GL Account Groups in Excel
      Charley Kyd
      Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,

      RELATED ARTICLESMORE FROM AUTHOR

      The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.
      Array Formulas

      How to Use Excel’s LET Function

      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.
      Accounting & Finance

      How to Set Up an Automatic Error-Checking System in Excel Reports

      Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.
      Downloads

      How to Stack and Shelve Dynamic Arrays

      To use Account Groups in Excel formulas, you first must define the groups. Here’s how to do it using either simple lists or Dynamic Arrays.
      Array Formulas

      How to Define General Ledger Account Groups in Excel

      We compare Excel's five columnar lookup functions.
      Downloads

      XLOOKUP vs VLOOKUP vs INDEX-MATCH vs SUMIFS

      How to aggregate named groups of GL accounts.
      Accounting & Finance

      How to Report GL Account Groups in Excel

      All Categories

      Latest Articles

      The LET function is the most powerful function that Microsoft has released for Excel in years. Here's an introduction to its features.

      How to Use Excel’s LET Function

      Array Formulas Charley Kyd - February 14, 2021
      0
      In the summer of 2020, Microsoft introduced the LET function for Excel 365—one of the most-significant new worksheet functions that Microsoft has introduced in...
      You can ratchet down errors in your Excel reports by using an Error Summary Table that uses conditional formatting to alert you to errors.

      How to Set Up an Automatic Error-Checking System in Excel Reports

      Accounting & Finance Charley Kyd - February 9, 2021
      0
      Decades ago, I worked as a cost accountant for a large company. But because our department received terrible reports, I wrote my own reports...using...
      Here's how to create an Excel array from two others, with the arrays stacked either one on top of the other, or side-by-side, like books on a shelf.

      How to Stack and Shelve Dynamic Arrays

      Downloads Charley Kyd - February 1, 2021
      0
      While using Excel 365 recently, I needed to create one dynamic array that would consist of two arrays, with one stacked on top of...
      Advertisement
      Advertisement
      ExcelUser.com, for business users of Microsoft Excel
      ABOUT US
      ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission.
      FOLLOW US
      • Contact
      • Terms of Use
      • Privacy Policy
      • Disclaimer
      • Earnings Policy
      • Reprints and Reposts
      © ExcelUser, Inc. Copyright © 2004 - 2020 by Charles W. Kyd, all rights reserved.
      MORE STORIES
      How to aggregate named groups of GL accounts.

      How to Report GL Account Groups in Excel

      January 17, 2021
      If you track Accounts Receivable the way most companies do—with Days Sales Outstanding in Receivables (DSO)—you probably know less about your receivables than you think. There's a better way, which also can improve your cash flow forecasts.

      Use Excel to Fix Your Broken AR Measure of Days Sales...

      February 17, 2020
      This simple invoicing system allows you to keep a list of products and prices in Excel, then use VLOOKUP or INDEX-MATCH to populate an invoice with the item and quantity you choose.

      Use Excel’s INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs

      August 25, 2018
      Edit with Live CSS
      Save
      Write CSS OR LESS and hit save. CTRL + SPACE for auto-complete.