Sum Monthly Data to Quarterly

[OandGmodeling]

Trying to Sum monthly data from a column in a different sheet to a quarterly output in a row on my summary page. Anybody know of a formula that I could use to do this? Thanks!

 

[DanteAmor]

You can give some examples and the expected result.

Use this,
Upload an excel range:
XL2BB - Excel Range to BBCode

 

[OandGmodeling]

I have one column in a different tab that displays monthly data. I need to SUM that monthly data into quarterly #'s in a row on a separate tab. I believe I can use SUM(Offset but am unsure of the exact formula

 

[DanteAmor]

I'm sorry, but I still don't understand what result you want. That's why I asked for examples so that with the examples you explain what you have and what result you want.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[OandGmodeling]

My apologies. I can't post the data since it is confidential but below is a very simple example. I need to take what is in column Y from one sheet and sum or average into a quarterly format like in the second sheet. Any help would be a appreciated.

Month	X	Y	Z
Jan-19		1
Feb-19		2
Mar-19		3
Apr-19		4
May-19		5
Jun-19		6
Jul-19		7
Aug-19		8
Sep-19		9
Oct-19		10
Nov-19		11
Dec-19		12
Jan-20		13
Feb-20		14
Mar-20		15
Apr-20		16
May-20		17
Jun-20		18
Jul-20		19
Aug-20		20
Sep-20		21
Oct-20		22
Nov-20		23
Dec-20		24
Jan-21		25
Feb-21		26
Mar-21		27
Apr-21		28
May-21		29
Jun-21		30
Jul-21		31
Aug-21		32
Sep-21		33
Oct-21		34
Nov-21		35
Dec-21		36
Jan-22		37
Feb-22		38
Mar-22		39
Apr-22		40
May-22		41
Jun-22		42
Jul-22		43

Mar-19​

Jun-19​

Sep-19​

Dec-19​

Mar-20​

Jun-20​

Sep-20​

Dec-20​

Mar-21​

Jun-21​

Sep-21​

Dec-21​

Mar-22​

Jun-22​

Sep-22​

Dec-22​

Mar-23​

Jun-23​

Sep-23​

Dec-23​

Mar-24​

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Quarterly Total

Quarterly Average

 

[DanteAmor]

Assuming the following:
On sheet 1
- The dates are in column A, they are really dates and not texts.
You can set date and with the cell format change to "mm-yyyy"
- The values in column Y.

Book1
	A																							X	Y	Z
1	Month																								VALUE
2	01/01/2019																								1
3	01/02/2019																								2
4	01/03/2019																								3
5	01/04/2019																								4
6	01/05/2019																								5
7	01/06/2019																								6
8	01/07/2019																								7
9	01/08/2019																								8
10	01/09/2019																								9
11	01/10/2019																								10
12	01/11/2019																								11
13	01/12/2019																								12
Sheet1

On sheet 2:
- The dates are in row 1 (same as the dates on sheet1)

Book1
	A	B	C	D	E
1		01/03/2019	01/06/2019	01/09/2019	01/12/2019
2		Q1	Q2	Q3	Q4
3	Quarterly Total	6	15	24	33
4	Quarterly Average	2	5	8	11
Sheet2

Cell Formulas
Range	Formula
B3:E3	B3	=SUMIFS(Sheet1!$Y$2:$Y$50,Sheet1!$A$2:$A$50,">="&EOMONTH(B$1,-3)+1,Sheet1!$A$2:$A$50,"<="&EOMONTH(B$1,0))
B4:E4	B4	=AVERAGEIFS(Sheet1!$Y$2:$Y$50,Sheet1!$A$2:$A$50,">="&EOMONTH(B$1,-3)+1,Sheet1!$A$2:$A$50,"<="&EOMONTH(B$1,0))

 

[OandGmodeling]

Thank you!

 

[DanteAmor]

You're welcome