Sum by Quarter in Excel: New and Efficient Techniques

Knowing how to sum by quarter is crucial in Excel because quarterly reporting has become a standard practice in many industries.

For sum by quarter, we can leverage a regular SUMIFS formula, a dynamic array formula, or a custom function in Excel.

The basic SUMIFS formula requires users to input individual formulas for each quarter, making it easy to learn and use.

A dynamic array formula simplifies the process by allowing users to specify the year for which they want to generate quarterly reports, along with the date and amount ranges in the table. The formula returns the output in one step, making it more user-friendly.

The custom function, derived from the dynamic array formula, uses the same parameters but offers the advantage of being used just like a regular function. This function can be utilized across the entire workbook, enhancing efficiency and convenience.

Sample Data and Basic Formula

The table below, spanning cells A1:B14, presents projected cash flow dates in column A and amounts (in millions) in column B.

DateAmount (In Million)
01-01-20245
01-02-20245
03-03-202410
03-04-202410
04-05-20240
04-06-20240
05-07-20245
05-08-20245
05-09-20246
06-10-20246
06-11-20244
07-12-20244
07-01-20256

As observed, the data spans two years, and our objective is to sum the cash flow amounts for each quarter within a specified year in Excel.

Therefore, we recommend using quarter start and end dates (e.g., 01/01/2024 to 31/03/2024) instead of specifying quarter numbers.

Step 1: Generating the Starting and Ending Dates for Each Quarter

In cell range D2:D5, enter the starting dates of each quarter.

In cell E2, enter the following formula to get the end date of the first quarter:

=EDATE(D2, 3)-1

Navigate to cell E2, click, and drag the bottom-right fill handle down until cell E5.

Sequence of quarter start and end dates in Excel

Step 2: Applying the SUMIFS Formula for Quarterly Sums

Enter the following SUMIFS formula in cell F2 to return the sum of cash flow amounts in the first quarter:

=SUMIFS(
   $B$2:$B$14, 
   $A$2:$A$14, ">="&D2, 
   $A$2:$A$14, "<="&E2
)

This follows the syntax:

=SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2)

Where:

  • sum_range: B$2:$B$14
  • criteria_range1: $A$2:$A$14
  • criterion1: “>=”&D2
  • criteria_range2: $A$2:$A$14
  • criterion2: “<=”&E2

Copy and paste this formula down to cell F5 by clicking and dragging the fill handle in cell F5 down.

Screencapture showing SUMIFS for sum by quarter range in Excel

These steps outline the basic formula for summing by quarter in Excel.

Sum by Quarter: Dynamic Array Formula in Excel

If you’re using Excel in Microsoft 365, you might prefer a dynamic array formula for summing by quarter.

The formula’s flexibility will exceed your expectations. You only need to specify the year for which you want to generate the quarterly sum, along with the range references for dates and amounts.

Here’s the formula:

=LET(
   year, 2024, 
   drange, A2:A14, 
   srange, B2:B14, 
   start, 
      EDATE(DATE(year, 1, 1), SEQUENCE(4, 1, 0, 3)), 
   end, 
      EDATE(DATE(year, 1, 1), SEQUENCE(4, 1, 3, 3))-1, 
   HSTACK(
      start, 
      end, 
      MAP(start, end, LAMBDA(s, e, SUMIFS(srange, drange, ">="&s, drange, "<="&e)))
   )
)

Insert this formula in cell D2.

Select D2:E5 and click the drop-down within the Number group in the Home tab, then select Short Date.

Example of Sum by Quarter Using Dynamic Array Formula in Excel

In this formula, you need to make three changes to use it in your dataset:

  1. Replace 2024 with the year of your choice.
  2. Replace A2:A14 with the date range, and B2:B14 with the amount range.

What makes this formula special is its flexibility. You can enter the year in any cell and replace “2024” in the formula with a reference to that cell. This way, you can quickly switch between years and effortlessly obtain quarterly sums for different years.

Formula Explanation

The Excel’s Sum by Quarter dynamic array formula employs the LET function to assign names to values or value expressions, enhancing the formula’s readability. Additionally, it improves performance by preventing repeated calculations.

Syntax:

=LET(name1, name_value1, name2, name_value2, name3, name_value3, name4, name_value4, name5, name_value5, calculation)

Where:

  • name1 is ‘year’ and name_value1 is ‘2024’: Represents the year for which we want to obtain the sum by quarter of the cash flow amounts.
  • name2 is ‘drange’ and name_value2 is A2:A14: Denotes the date range to summarize by quarter.
  • name3 is ‘srange’ and name_value3 is B2:B14: Represents the amount to sum by quarter.
  • name4 is ‘start’ and name_value4 is EDATE(DATE(year, 1, 1), SEQUENCE(4, 1, 0, 3)): Generates a sequence of each quarter’s starting dates.
  • name5 is ‘end’ and name_value5 is EDATE(DATE(year, 1, 1), SEQUENCE(4, 1, 3, 3))-1: Generates a sequence of each quarter’s ending dates.

Sum by Quarter Calculation (utilizing the assigned names and values):

HSTACK(
start,
end,
MAP(start, end, LAMBDA(s, e, SUMIFS(srange, drange, ">="&s, drange, "<="&e)))
)

The critical aspect of this calculation is the following SUMIFS formula:

SUMIFS(srange, drange, ">="&s, drange, "<="&e)

Where:

  • sum_range: srange
  • criteria_range1: drange
  • criterion1: “>=”&s (the first value in the ‘start’ date)
  • criteria_range2: drange
  • criterion2: “<=”&e (the first value in the ‘end’ date)
LET calculation Part Explained in Excel

The MAP formula iterates over each value in the start and end range, producing the quarterly sum.

The HSTACK function combines the start and end dates of the quarters with the quarterly sum calculated using the MAP function.

Sum by Quarter: Custom Function in Excel

We can transform the aforementioned dynamic array formula into a reusable function within a workbook in Excel.

The function syntax is as follows:

SUM_BY_QTR(year, drange, srange)

Where:

  • year: Represents the year for the quarterly report.
  • drange: Denotes the date range in the table.
  • srange: Represents the sum range (amount column range) in the table.

Steps:

To accomplish this, we utilize the LAMBDA function in the following syntax:

=LAMBDA(parameter1, parameter2, parameter3, calculation)

Let’s specify ‘year’ as parameter1, ‘drange’ as parameter2, and ‘srange’ as parameter3. The calculation will be the sum-by-quarter dynamic array formula. However, in that, you should remove the year, 2024, drange, A2:A14, srange, B2:B14, part from the beginning of the LET formula. Here it is:

=LAMBDA(year, drange, srange, LET(
   start,
      EDATE(DATE(year, 1, 1), SEQUENCE(4, 1, 0, 3)),
   end,
      EDATE(DATE(year, 1, 1), SEQUENCE(4, 1, 3, 3))-1,
   HSTACK(
      start,
      end,
      MAP(start, end, LAMBDA(s,e, SUMIFS(srange, drange, ">="&s, drange, "<="&e)))
   )
))

To create the custom function, copy this formula and open the workbook in which you want to use it.

  1. Go to the Formulas tab.
  2. Click Name Manager within the Defined Names group.
  3. In the Name Manager dialog box, click New.
  4. Type the name SUM_BY_QTR in the Name field.
  5. Select Workbook within the Scope field.
  6. In the Refer to field, replace the existing reference with the copied formula.
  7. Click OK and Close the Name Manager dialog box.

You are now set to use the SUM_BY_QTR() named function in that Excel workbook.

Example:

=SUM_BY_QTR(2024, A2:A14, B2:B14)

Don’t forget to format the first and second columns of the result to the Short Date.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.