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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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 Total6152433
4Quarterly Average25811
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))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top