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
    • Inflation & Deflation
    • Public Company Data
    • 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
    • Functions Explained
    • Logical Functions
    • Lookup Formulas and Functions
    • Math Functions
    • Range Names
    • Rounding Functions
    • Statistical Functions
    • Text Functions
  • Excel Strategies
    • Analytical Methods
    • Excel Productivity
    • Excel-Friendly OLAP
    • Visualization
  • Training & Solutions
  • Downloads
Sign in
Welcome!Log into your account
Forgot your password?
Password recovery
Recover your password
Search
Friday, March 24, 2023
  • 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 & SimulationInflation & DeflationPublic Company DataReportingTests & ValidationsTime Value of Money
      Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.
      Accounting & Finance

      How Fast Is Too Fast?

      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.
      Charting Methods

      Consumer Sentiment Suggests a 2022 Recession

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

  • Excel Methods
    • AllCharting MethodsConditional FormattingData ManagementError ReductionExcel DashboardsExcel TablesExcel’s Graphic ToolsPrintingReport TablesShortcutsVBAWorksheet Interaction
      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.
      Charting Methods

      Consumer Sentiment Suggests a 2022 Recession

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

      A shortage of truckers is one cause of the supply chain crisis in the US. This Excel chart suggests that the problem won't end soon.
      Blog

      An Excel Chart of a Major U.S. Supply Chain Problem

  • Formulas & Functions
    • AllAggregation FunctionsArray FormulasCounting FunctionsDate-Time FunctionsDates and TimesDynamic Array FormulasFinancial FunctionsFunctions ExplainedLogical FunctionsLookup Formulas and FunctionsMath FunctionsRange NamesRounding FunctionsStatistical FunctionsText Functions
      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      As inflation rises, so do mortgage interest rates—causing house prices to fall. Here's how Excel's PV function can help you estimate what you new house price will be.
      Blog

      Here’s How Inflation Could Affect the Value of Your Home

      Aggregation Functions

      How to Smooth Monthly Trends with Centered Moving Averages

      To reduce errors in reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to fix the problem with a standard deviation calculation.
      Error Reduction

      How to Foot and Cross-Foot Excel Reports in a Floating-Point World

  • Excel Strategies
    • AllAnalytical MethodsExcel ProductivityExcel-Friendly OLAPVisualization
      Aggregation Functions

      How to Smooth Data by Using the TREND Function

      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.
      Charting Methods

      Consumer Sentiment Suggests a 2022 Recession

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

      Aggregation Functions

      How to Smooth Monthly Trends with Centered Moving Averages

  • Training & Solutions
    • Use this unique Excel dashboard to track the general rate of inflation, your personal rate, and the rate of the goods and services you buy.
      Business & Economics

      Inflation Tracker Excel Dashboard with Consumer Prices

      Update this Excel dashboard with 15 key US mortgage interest rates every business day, with one command. Each chart shows 71 days of rates.
      Training & Solutions

      Mortgage Rates Excel Dashboard

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

      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 & Solutions

      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
8452
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

      Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.
      Accounting & Finance

      How Fast Is Too Fast?

      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.
      Charting Methods

      Consumer Sentiment Suggests a 2022 Recession

      Your data can contain many important, hidden patterns. But simple transformations in Excel can help to reveal them—as this example illustrates.
      Analytical Methods

      Simple Transformations Can Reveal Hidden Patterns in Your Data

      As inflation rises, so do mortgage interest rates—causing house prices to fall. Here's how Excel's PV function can help you estimate what you new house price will be.
      Blog

      Here’s How Inflation Could Affect the Value of Your Home

      To reduce errors in reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to fix the problem with a standard deviation calculation.
      Error Reduction

      How to Foot and Cross-Foot Excel Reports in a Floating-Point World

      Use this unique Excel dashboard to track the general rate of inflation, your personal rate, and the rate of the goods and services you buy.
      Business & Economics

      Inflation Tracker Excel Dashboard with Consumer Prices

      All Categories

      Latest Articles

      Growing too fast can be dangerous to your company's health. Use the Sustainable Growth Rate ratio to track your company's financial ability to grow.

      How Fast Is Too Fast?

      Accounting & Finance Charley Kyd - July 26, 2022
      0
      (Originally published in Inc Magazine.) What typically tops the list of worries of the chief executive officers of fast growing companies? Financing that growth, according...

      How to Smooth Data by Using the TREND Function

      Aggregation Functions Charley Kyd - January 17, 2022
      0
      Years ago, I read that Prof. William S. Cleveland had suggested that data could be smoothed by calculating a centered trendline through adjacent data—a...
      Two economists have introduced a new leading indicator, which predicts a recession soon. These Excel charts illustrate their insight. If they're correct, Excel users will be very busy in the months ahead.

      Consumer Sentiment Suggests a 2022 Recession

      Charting Methods Charley Kyd - December 17, 2021
      0
      In recent months, business websites have speculated about recessions and stagflation in 2022. These predictions could affect your Excel work significantly in the next few...
      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
      The SUMIFS function returns a sum for nearly any number of single criteria. But with SUMPFODUCT you can return the sum for many criteria listed in a Criteria List.

      How to Use SUMIFS with Criteria Lists, Summarizing Sales

      August 29, 2018
      In this final article of the SUMIFS, SUMPRODUCT series, you'll learn more reasons to use advanced multi-criteria lists in marketing and finance.

      Advanced SUMIFS Calculations with Criteria Lists

      July 11, 2018
      Here's how to use Excel to figure out whether you're making or losing money on the cash you borrow, why it matters, and what you can do about it.

      Weighing Your Debt Load

      December 17, 2019
      Edit with Live CSS
      Save
      Write CSS OR LESS and hit save. CTRL + SPACE for auto-complete.