Sum Monthly Data to Quarterly

OandGmodeling

New Member
Joined
Apr 5, 2019
Messages
8
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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

OandGmodeling

New Member
Joined
Apr 5, 2019
Messages
8
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,726
Office Version
2007
Platform
Windows
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

New Member
Joined
Apr 5, 2019
Messages
8
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.



MonthXYZ
Jan-191
Feb-192
Mar-193
Apr-194
May-195
Jun-196
Jul-197
Aug-198
Sep-199
Oct-1910
Nov-1911
Dec-1912
Jan-2013
Feb-2014
Mar-2015
Apr-2016
May-2017
Jun-2018
Jul-2019
Aug-2020
Sep-2021
Oct-2022
Nov-2023
Dec-2024
Jan-2125
Feb-2126
Mar-2127
Apr-2128
May-2129
Jun-2130
Jul-2131
Aug-2132
Sep-2133
Oct-2134
Nov-2135
Dec-2136
Jan-2237
Feb-2238
Mar-2239
Apr-2240
May-2241
Jun-2242
Jul-2243


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​
Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1
Quarterly Total
Quarterly Average
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,726
Office Version
2007
Platform
Windows
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
AXYZ
1MonthVALUE
201/01/20191
301/02/20192
401/03/20193
501/04/20194
601/05/20195
701/06/20196
801/07/20197
901/08/20198
1001/09/20199
1101/10/201910
1201/11/201911
1301/12/201912
Sheet1


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

Book1
ABCDE
101/03/201901/06/201901/09/201901/12/2019
2Q1Q2Q3Q4
3Quarterly Total
6
15
24
33
4Quarterly Average
2
5
8
11
Sheet2
Cell Formulas
RangeFormula
B3:E3B3=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:E4B4=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))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,142
Messages
5,466,921
Members
406,509
Latest member
David504

This Week's Hot Topics

Top