How to sum monthly data in Excel into quarterly

Question

As the title says, I would like to convert monthly Excel data into quarterly by summing the 3 previous cells (i.e merge January, February and March 2003 data into Q1 2003)

Much thanks in advance

Answer 1

There are many ways of doing this, here are few examples:

• If there are only month names:

---

=SUM(("Q"&ROUNDUP(MONTH(A1:A12&0)/3,)=D1)*(B1:B12))

---

• If the dates are true dates then:

Sample data used for the following formulas:

DATES	VALUES
January 31, 2003	114
February 1, 2003	163
March 1, 2003	178
April 1, 2003	176
May 1, 2003	125
June 1, 2003	191
July 1, 2003	118
August 1, 2003	104
September 1, 2003	148
October 1, 2003	109
November 1, 2003	162
December 1, 2003	123

---

=SUM(("Q"&ROUNDUP(MONTH(A1:A12&0)/3,)&" "&YEAR(A1:A12)=D1)*(B1:B12))

---

Or if applicable using GROUPBY() to get all the quarters :

---

=GROUPBY("Q"&ROUNDUP(MONTH(A1:A12&0)/3,)&" "&YEAR(A1:A12),B1:B12,SUM,,0)

---

If one don't have access to GROUPBY() then using BYROW() or MMULT()

=LET(
     _Quarters, "Q"&ROUNDUP(MONTH(A1:A12&0)/3,),
     _Years, YEAR(A1:A12),
     _Unique, UNIQUE(_Quarters&" "&_Years),
     _Sum, MMULT(N(_Unique=TOROW(_Quarters&" "&_Years)),B1:B12),
     SORT(HSTACK(_Unique, _Sum)))

---

=LET(
     _Quarters, "Q"&ROUNDUP(MONTH(A1:A12&0)/3,),
     _Years, YEAR(A1:A12),
     _Unique, UNIQUE(_Quarters&" "&_Years),
     _Sum, BYROW(_Unique,LAMBDA(x, SUM((x=_Quarters&" "&_Years)*B1:B12))),
     SORT(HSTACK(_Unique, _Sum)))

---

Note: Since there is no sample mock up data, hence you may need to change the range and ranges as per your suit.

---

Answer 2

Please provide a basic example of your data when asking the question. As is, your question is quite nebulous and it is unclear what form your sheet is in.

As a very basic example, assuming you have a single column of data with each cell representing a single month in column A, you can easily sum each set of 3 cells in column B. In B3, you can use the formula

=SUM(A1:A3)

and then copy it to every B cell that is divisible by 3 (6, 9, 12, 15, etc.)

If you want something you can simply copy throughout the column, you could use

=IF(MOD(ROW(B3), 3) = 0, SUM(A1:A3), "")

as it will skip all non 3 divisible rows.

Answer 3

If your dates are formatted as dates you could create a pivot table that handles the dates allowing you to sum by flexible periods (year, quarter, period). Just highlight your data and select Insert pivot table and drag the date into the appropriate fields. Then right click and select "group" and you'll see all the date options

You can also create a table where you do a lookup on the first day of each month with a non-exact match, and label the rows with the respective quarters then do a sumifs from those quarters.