where data is an Excel Table in the range B5:D16, and the quarters in column E are generated by another formula explained below.
Background study
What is an Excel Table (3 min. video) Introduction to structured references (3 min. video) Excel Tables (overview)
Calculating quarters
The first step in this problem is to generate a quarter for each date in column B. In the table shown, column D is a helper column with quarter numbers calculated with a separate formula. The formula in D5, copied down, is: Note: because we are using an Excel Table to hold the data, we automatically get the structured reference seen above. The reference [@Date] means: current row in Date column. If you are new to structured references, see this short video: Introduction to structured references. The MONTH function returns a month number between 1-12 for each date, which is divided by 3. The ROUNDUP function is then used to round the result to the nearest whole number. This formula is explained in more detail here.
SUMIFS solution
The next step in the problem is to add up the amounts in column C using the quarter numbers in column D. This can easily be done with the SUMIFS function. The SUMIFS function is designed to sum values in ranges conditionally based on multiple criteria. The signature of the SUMIFS function looks like this: Notice the sum_range comes first, followed by range/criteria pairs. Each range/criteria pair of arguments represents another condition. In this case, we need to configure SUMIFS to sum values by quarter using just one condition: we need to check the quarter in column D for a match in F5. We start off with the sum_range: Next, we add criteria as a range/criteria pair, where criteria_range1 is the Date column, and criteria1 is the quarter number in column F: As the formula is copied down, we get a total for each quarter in column F.
SUMPRODUCT without helper
To solve this problem without a helper column you can use a formula like this: Working from the inside out, the first part of the expression inside the SUMPRODUCT function generates a quarter number for each date in the Date column like this: This is basically the same formula used above, the difference is that we feed the MONTH function the entire data[Date] column instead of one cell. Because there are 12 dates in the column, we get back an array that contains 12 month numbers like this: This array is delivered to the ROUNDUP function as the number argument: And ROUNDUP returns an array of 12 quarter numbers: Note: we are using a very small data set in this example for simplicity, but the same approach will work with hundreds or thousands of dates. Next, the array from ROUNDUP is compared to F5 and the result is an array that contains 12 TRUE and FALSE values. When this array is multiplied by data[Amount], the math operation changes the TRUE and FALSE values to 1s and 0s. At this point, we have: Multiplying the two arrays together results in a single array. In this array, only amounts associated with quarter 1 survive — amounts for other quarters are effectively “zeroed-out”: With just one array to process, SUMPRODUCT sums the values in the array and returns a final result, 707. As the formula is copied down column G, it returns a total for each quarter, with no helper column needed. Note: we use the SUMPRODUCT function in this formula for compatibility with older versions of Excel. In the current version of Excel, you can use the SUM function instead with the same result.
Dynamic array solution
In the latest version of Excel, which supports dynamic array formulas, it is possible to create a single all-in-one formula that builds the entire summary table, including headers, like this: The LET function is used to assign values to five variables: dates, amounts, quarters, uquarters, and totals. First, we assign values to dates and amounts like this: Technically, we could just use the references data[date] and data[Amount] throughout the formula, but defining variables at the start keeps all worksheet references at the top of the code where they can be easily changed. You can easily adapt the formula to work with a different data set by editing just these two references. Next, the value for quarters is created like this: Here the MONTH function and the ROUNDUP function are used to calculate a quarter for each date in data[Date]. This formula is explained in more detail here. Because the table contains 12 rows of data, the result is an array with 12 quarter values like this: In the next line we define uquarters (unique quarters) like this: Note we are just hardcoding the value as an array constant. Alternately, we could run quarters through the UNIQUE function. Either way, the result is a vertical array of four quarter numbers. At this point, we are ready to sum amounts by quarter. We do this with the BYROW function which calculates the sums and assigns the result to the variable counts for each year like this: BYROW runs through uquarters row by row. At each row, it applies this calculation: The value for r is the quarter number in the “current” row. Inside the SUM function, r is compared to quarters. Since quarters contains 12 values, the result is an array with 12 TRUE and FALSE results, which is then multiplied by amounts. The math operation automatically converts the TRUE and FALSE values to 1s and 0s, and the zeros effectively “zero out” amounts in other quarters. The SUM function then sums the resulting array and returns the result. When BYROW is finished, we have an array with six sums, which is assigned to totals: Finally the HSTACK and VSTACK functions are used to assemble a complete table: At the top of the table, the array constant {“Quarter”,“Total”} creates a header row. The HSTACK function combines uquarters and totals horizontally, and VSTACK combines the header row and the data to make the final table. The final result spills into multiple cells on the worksheet.
Pivot Table solution
A pivot table is another excellent solution when you need to summarize data by year, month, quarter, and so on, because it can do this kind of grouping for you without any formulas at all. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
