All data is in anExcel Tablenameddatain the range B5:E16.
This problem can be solved with theSUMIFS functionand the helper column, orwithouta helper column using theSUMPRODUCT function.
Both approaches are explained below.
Finally, you could also use anall-in-one dynamic array formulain the latest version of Excel.
In the table shown, column D is ahelper columnwith quarter numbers calculated with a separate formula.
The reference [@Date] means: current row in Date column.
If you are new to structured references, see this short video:Introduction to structured references.
TheMONTH functionreturns a month number between 1-12 for each date, which is divided by 3.
TheROUNDUP functionis then used to round the result to the nearest whole number.
This formula isexplained in more detail here.
The SUMIFS function is designed to sum values in rangesconditionallybased on multiple criteria.
The signature of the SUMIFS function looks like this:
Notice thesum_rangecomes first, followed byrange/criteriapairs.
Each range/criteria pair of arguments represents another condition.
At this point, we have:
Multiplying the two arrays together results in a single array.
Note: we use the SUMPRODUCT function in this formulafor compatibility with older versions of Excel.
In thecurrent versionof Excel, you might use theSUM functioninstead with the same result.
you might easily adapt the formula to work with a different data set by editing just these two references.
This formula isexplained in more detail here.
Alternately, we could runquartersthrough theUNIQUE function.
Either way, the result is a vertical array of four quarter numbers.
At this point, we are ready to sum amounts by quarter.
Inside the SUM function,ris compared toquarters.
TheSUM functionthen sums the resulting array and returns the result.
The HSTACK function combinesuquartersandtotalshorizontally, and VSTACK combines the header row and the data to make the final table.
The final resultspillsinto multiple cells on the worksheet.
For a side-by-side comparison of formulas vs. pivot tables, see this video:Why pivot tables.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
