Hi ,

Excel Formulas to Summarize Monthly Data into Quarters

Back in my accounting days I regularly prepared reports that summarized monthly data into quarters.

Back then, we used the SUM function with Boolean (TRUE/FALSE) logic, which worked fine, but, there are better ways to do it now that we have dynamic arrays.

In this tutorial I’ll cover both options and you can choose the method that works best for you.

Note: The examples cover data in a horizontal layout, but in the example file you can download below you’ll find the equivalent formulas for data in vertical layout.

Download Example Files

Get Quarterly Totals Formula using SUM

Accountants love to store data in columns for each month and accounts/items in the row labels like the example data below:

Note: the dates in row 4 are date serial numbers formatted to only show mmm-yy, as you can see in the formula bar:

We can sum the data into quarters using the following formula in the top left cell of the table

=SUM($C5:$N5*(ROUNDUP(MONTH($C$4:$N$4)/3,0)=COLUMN(A:A)))

Which can then be copied across for the remaining quarters, and down for the other items returning a table of values summarized by quarter:

Each argument in the formula explained

=SUM($C5:$N5*(ROUNDUP(MONTH($C$4:$N$4)/3,0)=COLUMN(A:A)))

The range referenced by SUM is simply the range we want to sum by quarters.

The absolute referencing on the columns enables the formula to be copied down and across while remaining on the correct columns:

=SUM($C5:$N5

Taking row 5, it evaluates to an array of the following values

{9,5,4,4,9,4,2,8,1,8,8,3}

Next, the MONTH function returns the month number of each date in row 4.

ROUNDUP(MONTH($C$4:$N$4)/3,0)

Dividing the result by 3 and rounding it to zero decimal places results in an array of the quarter numbers

{1,1,1,2,2,2,3,3,3,4,4,4}

We use the COLUMN function to return the quarter numbers one by one

COLUMN(A:A)

The first formula references A:A, returning 1. When the formula is copied across the columns, the range referenced by COLUMN updates to reference B:B, returning 2 and so on.

When ROUNDUP(MONTH($C$4:$N$4)/3,0) = COLUMN(A:A) is evaluated it looks like this:

{1,1,1,2,2,2,3,3,3,4,4,4} = 1

And we get an array of TRUE and FALSE values:

{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

When we multiply these by the array returned by the first range, $C5:$N5, the TRUE and FALSE evaluate to their numeric equivalents of 1 and 0. So we have this:

=SUM( {9,5,4,4,9,4,2,8,1,8,8,3} * {1,1,1,0,0,0,0,0,0,0,0,0} )

Which evaluates to:

=SUM( {9,5,4,0,0,0,0,0,0,0,0,0} )

=18

Tip: we can improve this formula with the LET function which enables you to name variables inside the formula, making it easier to write and make sense of when you come back to it in a few months’ time:

=LET(          
               quantity, $C5:$N5,         
               mths, $C$4:$N$4,  
               qtrs, ROUNDUP(MONTH(mths) / 3, 0),            
               currentQtr, COLUMN(A:A),
               SUM(quantity * (qtrs = currentQtr))            
             )

Download the example file above for the equivalent formula for data in a vertical layout.

Summarize Data into Quarters using Dynamic Arrays

With the introduction of dynamic array functions in Excel, we* can now write a single formula for each row/item in the table using the BYCOL and LAMBDA functions.

*BYCOL and LAMBDA are currently only available to users with a Microsoft 365 subscription. If that’s not you, try the PivotTable Grouping method here.

We leverage the same ROUND(MONTH(… formula component to convert the dates to their equivalent quarters and nest it in the BYCOL and LAMBDA functions like so

=BYCOL({1,2,3,4}, LAMBDA(qtr, SUM(C5:N5*(ROUNDUP(MONTH($C$4:$N$4)/3,0)=qtr))))

The result is a spilled array for each row, as you can see in row 14 of the image below

Each argument in the formula explained:

=BYCOL({1,2,3,4}, LAMBDA(qtr, SUM(C5:N5*(ROUNDUP(MONTH($C$4:$N$4)/3,0)=qtr))))

BYCOL returns the values of an array/range, one column at a time and provides them to the LAMBDA function, which then aggregates them.

The BYCOL function syntax is

BYCOL(array, lambda(column))

And in this example, we’re using a LAMBDA function with SUM like so

BYCOL(quarters, LAMBDA(quarters name, SUM(columns that match the quarters, one quarter at a
time)))

The first argument for BYCOL is the array of quarter numbers

=BYCOL({1,2,3,4}

IMPORTANT: the comma separator between each number in the BYCOL array indicates a new column of data. A semi-colon here would indicate a new row of data.

The first argument for LAMBDA is simply the name I’ve given to the quarter numbers array in the first argument of BYCOL i.e. ‘qtr’

The range referenced by SUM is the range we want to sum by quarters

SUM(C5:N5

Taking row 5, it evaluates to an array of the following values

{9,5,4,4,9,4,2,8,1,8,8,3}

Next, the MONTH function returns the month number of each date in row 4.

ROUNDUP(MONTH($C$4:$N$4)/3,0)

Dividing the result by 3 and rounding it to zero decimal places results in an array of the quarter numbers

{1,1,1,2,2,2,3,3,3,4,4,4}

Remember, the comma separator between each value in the array indicates a new column. We then use the qtr array, one column at a time to return an array of values for SUM to aggregate.

For example, the first column of qtr is applied like so

SUM({9,5,4,4,9,4,2,8,1,8,8,3} * ({1,1,1,2,2,2,3,3,3,4,4,4}=1))

Returning

SUM( {9,5,4,4,9,4,2,8,1,8,8,3} * {TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE} )

And when TRUE and FALSE values have a math operation applied to them, in this case multiply, they convert to their numeric equivalents of 1 and 0 returning this

SUM( {9,5,4,4,9,4,2,8,1,8,8,3} * {1,1,1,0,0,0,0,0,0,0,0,0} )

Which evaluates to

SUM( {9,5,4,0,0,0,0,0,0,0,0,0} )

=18

This value is returned to the first cell in the spilled array.

BYCOL then gives SUM the next quarter and the process starts again

SUM({9,5,4,4,9,4,2,8,1,8,8,3} * ({1,1,1,2,2,2,3,3,3,4,4,4}=2))

Which evaluates like so

SUM({9,5,4,4,9,4,2,8,1,8,8,3} * ({0,0,0,1,1,1,0,0,0,0,0,0}))

SUM( {0,0,0,4,9,4,0,0,0,0,0,0} )

=17

This value is returned to the second cell in the spilled array.

Tip: And again with the LET function so it’s easier to write and make sense of when you come back to it in a few months’ time

=LET(
               qtrArray, {1, 2, 3, 4},
               qtrs, ROUNDUP(MONTH($C$4:$N$4) / 3, 0),
               quantity, C5:N5,
               BYCOL(
                 qtrArray
                 LAMBDA(qtrArray,SUM (data * (quantity = qtrArray)))
               )
              )

Download the example file above for the equivalent formula for data in a vertical layout.

Single Formula to Sum Data into Quarters and Categories

The previous formula is ok, but I still have to copy it down the rows.

It would be better if it automatically spilled the row and column results, so I don’t have to copy and paste at all!

However, I’d reached the limit of my current formula writing skills.

So, I reached out to the most incredible formula writers I know: Sergei Baklan and Peter Bartholomew to see if they were up for the challenge. Of course, they were.

In fact, they were so up for it they provided me with a total of 8 different formulas.

Plus, Sergei provided Power Query, Office Scripts and Python in Excel options, which you can see in the files available to download from the link above!

Rather than looking at all their solutions, we’ll just look at a few, because they each offer something different.

Note: These solutions require dynamic array functions BYCOL or BYROW, which are currently only available with a Microsoft 365 license.

Horizontal Layout Solution by Peter Bartholomew

Using the data table from my previous examples, Peter’s final offering is this very succinct formula:

=LET(
               arrangedByQtr, WRAPCOLS(TOCOL(quantity), 3),
               listQtyByQtr, BYCOL(arrangedByQtr, LAMBDA(x, SUM(x))),
               qtyByQtr, WRAPROWS(listQtyByQtr, 4),
               HSTACK(qtyByQtr, BYROW(qtyByQtr, LAMBDA(x, SUM(x))))
              )

Which returns the spilled array in cells C14:G18 shown below

Each argument in the formula explained

This formula processes the values in the table, unpivots them, groups them into quarters, and sums them up for each quarter. Then presents the quarterly sums along with an annual total.
 

1. Unpivot and Group by Quarters (arrangedByQtr):

• TOCOL(quantity): This converts the 'quantity' (which is the name for the range C5:N9) into a single column.
• WRAPCOLS(TOCOL(quantity), 3): This then wraps (or reshapes) this single column into multiple columns, each having 3 rows. i.e. it rearranges the data into quarters, where each quarter has 3 months. The result is stored in 'arrangedByQtr', as shown below

2. Sum Each Quarter (listQtyByQtr):

• BYCOL(arrangedByQtr, LAMBDA(x, SUM(x))): This applies the SUM to each column of 'arrangedByQtr'. This will produce a list where each item is the sum of a quarter, stored in 'listQtyByQtr', as shown below:

3. Arrange Quarterly Sums into Rows (qtyByQtr):

• WRAPROWS(listQtyByQtr, 4): This reshapes 'listQtyByQtr' into multiple rows, with each row having 4 values.  i.e. the data across years, where each year has 4 quarters. The result is stored in 'qtyByQtr', as shown below:

4. Add Row-wise Total:

• HSTACK(qtyByQtr, BYROW(qtyByQtr, LAMBDA(x, SUM(x)))): This horizontally stacks the 'qtyByQtr' array with the row-wise sums of 'qtyByQtr'. The 'BYROW(qtyByQtr, LAMBDA(x, SUM(x)))' function calculates the sum for each row in 'qtyByQtr'.

The overall output of the formula is an array where each row represents the summed quantities for each year (broken down by quarters), followed by a total quantity for that year.

Horizontal Layout Solution by Sergei Baklan

The image below displays the result of a single formula in cell B13 that spills the results for the entire table, including column and row labels:

It’s constructed using this single formula based on the horizontal layout used in the examples above

=LET(
               data, DROP(range, 1, 1),
               total, BYROW(data, LAMBDA(v, SUM(v))),
               months, DROP(TAKE(range, 1), , 1),
               MonthsInQuarters, ROUNDUP(MONTH(months) / 3, 0) & "Q" & RIGHT(YEAR(months), 2),
               quarters, UNIQUE(MonthsInQuarters, 1),
               nRows, ROWS(data),
               nCols, COLUMNS(quarters),
               sums, MAKEARRAY(
                 nRows,
                 nCols,
                 LAMBDA(n, m,
                  INDEX(
                    BYROW(
                    data,
                     LAMBDA(v,
                      INDEX(
                        MMULT(v, --(TRANSPOSE(MonthsInQuarters) = quarters)), 1, m))
                     ), n,1)
                 )),
               HSTACK(
                TAKE(range, , 1),
                VSTACK(quarters, sums),
                VSTACK("Total", total)
               )
             )

Each argument in the formula explained:

This formula processes a table of data with monthly columns, aggregates the data into quarterly columns, and appends a total for each row at the end. It can be explained as follows
 

1. Initialize Data:

• data, DROP(range, 1, 1): Removes the first row and first column from the 'range'. This gets the data excluding the header row.
• total, BYROW(data, LAMBDA(v, SUM(v))): Calculates the sum of each row in the 'data'.
• months, DROP( TAKE(range,1),,1): Takes the first row of the 'range' (which contains dates) and drops the first column.

2. Convert Months to Quarters:

• MonthsInQuarters, ROUNDUP(MONTH(months)/3,0) & "Q" & RIGHT(YEAR(months),2): Converts each month in 'months' to its respective quarter representation, e.g., "1Q23" for January 2023.
• quarters, UNIQUE(MonthsInQuarters,1): Extracts the unique quarters from the 'MonthsInQuarters' array.

3. Determine Dimensions:

• nRows, ROWS(data): Counts the number of rows in 'data'.
• nCols, COLUMNS(quarters): Counts the number of unique quarters.

4. Calculate Quarterly Sums:

• sums, MAKEARRAY(...): Creates an array of size 'nRows x nCols' where each element represents the sum of values for a specific quarter.
• The inner 'MMULT' function multiplies each row's values with a matrix indicating which month belongs to which quarter. The result of this multiplication gives the quarterly sum for each row.
   

5. Combine Results:

• HSTACK(...): Horizontally stacks the following:
  • The first column of the original 'range' (The column labels).
  • A vertically stacked array consisting of the unique 'quarters' followed by the 'sums'.
  • A vertically stacked array consisting of the label "Total" followed by the row totals ('total').

The final output is a table where the data is aggregated on a quarterly basis, with the last column showing the total for each row.

Sergei also offered two more formula options, one using a thunk and another using MAKEARRAY.

They can be downloaded from the workbook download link above.
 

Vertical Layout by Peter Bartholomew

The final challenge I only put to Peter was, what if we needed to allow for more than 12 months and a variable number of categories using the vertical data layout shown below:

This is Peter's solution.

=LET(
               m, 1 + QUOTIENT(ROWS(quantityV) - 1, 3),
               expanded, EXPAND(quantityV, 3 * m, , 0),
               arrangedByQtr, WRAPROWS(TOCOL(expanded, , TRUE), 3),
               listQtyByQtr, BYROW(arrangedByQtr, SUMλ),
               qtyByItem, WRAPCOLS(listQtyByQtr, m),
               qtyByItem
             )

Each argument in the formula explained:

The formula uses the 'LET' function, which allows for the creation of named intermediate variables within a formula, helping to break down complex calculations.
 

1. Calculate the Number of Columns:

• m, 1 + QUOTIENT(ROWS(quantityV) - 1, 3): The 'QUOTIENT' function divides the number of rows in 'quantityV' minus one by 3 and returns the integer portion of the division. Adding 1 to this result gives 'm', which represents the number of columns required when the data is grouped in sets of 3 rows.
   

2. Expand the Data:

• expanded, EXPAND(quantityV, 3 * m, , 0): The 'EXPAND' function likely enlarges the range of 'quantityV' to have (3 * m) rows. Any new cells created by this expansion are filled with zeros.
   

3. Convert to Single Column and Group by Threes:

• arrangedByQtr, WRAPROWS(TOCOL(expanded,,TRUE), 3): 'TOCOL(expanded,,TRUE)' converts the 'expanded' range into a single column, ignoring any empty cells.
• WRAPROWS(..., 3) then takes this single column and wraps it into multiple rows, with each row having 3 values. This is effectively grouping the values in sets of three.
   

4. Sum Each Group:

• listQtyByQtr, BYROW(arrangedByQtr, SUMλ): For each row (which has 3 values) in 'arrangedByQtr', the sum of those values is calculated using 'SUMλ'. The result is a single column where each row represents the sum of a group of three values from the original data.
   

5. Organize Sums by Item:

• qtyByItem, WRAPCOLS(listQtyByQtr, m): This takes the single column of sums ('listQtyByQtr') and wraps it into multiple columns, each having 'm' rows. If 'quantityV' had rows representing different items and columns representing different time periods (e.g., months), then 'qtyByItem' would have rows representing items and columns representing quarters.
   

6. Output the Result:

• The final output of the formula is 'qtyByItem', which is an array where each row represents a quarter, and each column represents the sum of quantities over categories.

In summary, this formula aggregates data from 'quantityV' over groups of three (i.e., summing monthly data into quarters) and then presents these aggregated sums in a table where each row represents a quarter, and each column represents a category.
 

Too Hard? Use a PivotTable

If all those formulas are hurting your head, there is an easier way using a PivotTable grouping.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub

Our Courses

Excel Dashboards

Dashboards provide insight, analysis and alerts by displaying key trends, comparisons and data in interactive reports. The reader can filter data and change views to find answers to their questions and problems.    Read More >

Power BI

Microsoft’s latest business intelligence (BI) & data visualization tool enables you to quickly develop stunning interactive dashboards. Embed Excel charts, tables, Pivot Tables and dashboards in your Power BI dashboards, then securely share your dashboards to any device.    Read More >

Advanced Excel Formulas

Get quickly up to speed with Excel's functions.  This course covers the fundamentals for each function, and less obvious uses for them. It's these more advanced techniques that will really set you apart from the crowd.    Read More >

Excel Tables

Make huge efficiency gains by using dynamic named ranges to automatically update formulas, Pivot Tables and charts. Automate formatting like banded rows or columns; sorting and filtering; and totals and statistics.  Everything you need to work with Tables in less than 1 hour.    Read More >

Pivot Tables : Quick Start

This short course will quickly have you building your own Pivot Tables with confidence. Start with the ideal data layout for creating Pivot Tables. Then move on to formatting, aggregation, pivot charts, filters, slicers and much more.    Read More >

Power Query

Get data from almost any source (a database, the web, Excel, Sharepoint, Salesforce, OData etc), then clean, transform, merge and append data to your query. Load the data into Excel or Power Pivot for analysis, report preparation or export.    Read More >

Power Pivot & DAX

Import millions of rows of data, create relationships between different data sources, and build interactive reports. Quickly transform huge volumes of data into meaningful information to get the answers you need.    Read More >

Excel Expert

Designed to take you from zero to hero so you can confidently list 'Advanced Excel Skills' on your resume/CV. Starting with the essentials, then covering must-know skills like best workbook design, advanced formulas, pivot tables, conditional formatting, macros and much more.    Read More >

Financial Modeling

Learn the core concepts of the three primary financial statements : Income Statement, Balance Sheet and Cash Flow Statement. By understanding core concepts you can avoid common mistakes when working with these three statements.  This course breaks down the modelling process into small segments that are easily digestible.    Read More >

Excel for Operations Management

Ideal for those involved in managing the daily operations of a business. Covering tasks such as calculating breakeven, optimizing shipping, allocating funds to maximize NPV/profit, or managing staff scheduling. Includes an introduction to Excel Solver and how to create Gantt Charts.    Read More >

Excel VBA

Are you tired of spending hours on tedious, manual tasks in Excel? Do you want to boost your productivity and take your skills to the next level? If you're nodding your head, then you're in the right place.
Learn Excel VBA and save hours of time even if you have zero prior experience with programming.    Read More >

Pivot Tables

Learning to use Pivot Tables will allow you to quickly summarise and analyse large amounts of data - with just a few clicks of your mouse. Quickly transform data into meaningful information, and create reports that are easy to update and change.    Read More >

Excel, Word & Outlook

Choose just one of these, or choose any combination of the three. Learn the core software tools any professional needs in a modern office environment.    Read More >

Excel for Decision Making

Use Excel's existing tools to make business decisions under uncertainty by performing Monte Carlo simulations, using Decision Trees and Forecasting with the Data Analysis Toolpak. Ideal for executives, analysts and all levels of business school students.    Read More >

Excel for Customer Service

Designed for professionals working in customer service or call center environments. Efficiently and accurately collect and analyze data. Create forecasts and generate clear, meaningful charts and reports. Boost your skills in handling customer service and call center information.    Read More >

Excel for Finance

Leverage Excel tools for financial analysis including Time value of Money functions, What-If analysis, Goal Seek, Amortization, Depreciation, and Solver Ideal for accountants, executives, analysts and all levels of business school students.    Read More >

Excel Analysis ToolPak

Statistics for the non-statistician. Quickly and efficiently calculate and report the statistics of data and make decisions based on statistical output. Use the Excel Analysis Toolpak to generate descriptive statistics, histograms, correlations, regression, rank & percentile, random numbers, and sampling.    Read More >