Hi - Dave here.
How can you sum values by quarter with an Excel formula?
There are a few ways to approach this problem. One option is to use the SUMIFS function with a helper column, as seen in the worksheet below. The formula in G5 is:
=SUMIFS(data[Amount],data[Qtr],F5)
If you want to sum values by quarter
without a helper column, you'll need a more advanced formula like this:
=SUMPRODUCT((ROUNDUP(MONTH(data[Date])/3,0)=F5)*data[Amount])
This formula will calculate quarters internally and returns the same result.
[
download the workbook and read the full explanation]
The article above explains both approaches and the Excel workbook is attached. I've also included an all-in-one dynamic array formula that builds the entire summary table in one step. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
