How to group/sum across dated columns?

[awenner]

I can't believe there isn't an easier way to do this ...

I have my data set up as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[/TR]
[TR]
[TD]Revenue beginning month[/TD]
[TD]100[/TD]
[TD]105[/TD]
[TD]110[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]Gains[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Losses[/TD]
[TD](5)[/TD]
[TD](5)[/TD]
[TD](5)[/TD]
[TD](5)[/TD]
[/TR]
[TR]
[TD]Revenue end of month[/TD]
[TD]105[/TD]
[TD]110[/TD]
[TD]115[/TD]
[TD]120[/TD]
[/TR]
</tbody>[/TABLE]


The list of columns extends to the right for up 60 months.

Is there a way for me to group and sum these columns by quarter or year? (See example)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]Revenue beginning month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gains[/TD]
[TD]30[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Losses[/TD]
[TD](15)[/TD]
[TD](5)[/TD]
[/TR]
[TR]
[TD]Revenue end of month[/TD]
[TD] 330[/TD]
[TD]120[/TD]
[/TR]
</tbody>[/TABLE]


I'm running Excel 2016 on MacOS, so I can't use the pivot table wizard (not implemented )

 

[NiMip]

I might be over complicating things... but in another row I would report which quarter the month is in, and then create an additional table which each quarter as a heading and do a SUMIF.
To get the quarter and year from the given month in cell B1, could use =INDEX({"Q1";"Q2";"Q3";"Q4"},LOOKUP(MONTH(B1),{1;4;7;10},{1;2;3;4}))&" "&TEXT(B1,"YYYY")

An alternative to the additional SUMIF table is to reorient your data and use Subtotal under the Data>Outline tab. If you had your data arranged as follows:

[TABLE="class: grid, width: 631, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Revenue beginning month[/TD]
[TD]Gains[/TD]
[TD]Losses[/TD]
[TD]Revenue end of month[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-17[/TD]
[TD]Q1 2017[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]Feb-17[/TD]
[TD]Q1 2017[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-17[/TD]
[TD]Q1 2017[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]115[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-17[/TD]
[TD]Q2 2017[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: right"]May-17[/TD]
[TD]Q2 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jun-17[/TD]
[TD]Q2 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jul-17[/TD]
[TD]Q3 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Aug-17[/TD]
[TD]Q3 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sep-17[/TD]
[TD]Q3 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Oct-17[/TD]
[TD]Q4 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Nov-17[/TD]
[TD]Q4 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Dec-17[/TD]
[TD]Q4 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The Subtotal tool would do this, with "At each change in:" as column B, and "Add subtotal to:" ticked for Gains, Losses and Revenue end of Month. If you had it pre-prepared, you could put the values into this and the totals would auto-update.[TABLE="class: grid, width: 631, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Revenue beginning month[/TD]
[TD]Gains[/TD]
[TD]Losses[/TD]
[TD]Revenue end of month[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-17[/TD]
[TD]Q1 2017[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]Feb-17[/TD]
[TD]Q1 2017[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-17[/TD]
[TD]Q1 2017[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]115[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q1 2017 Total[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]330[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-17[/TD]
[TD]Q2 2017[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: right"]May-17[/TD]
[TD]Q2 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jun-17[/TD]
[TD]Q2 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q2 2017 Total[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: right"]Jul-17[/TD]
[TD]Q3 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Aug-17[/TD]
[TD]Q3 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Sep-17[/TD]
[TD]Q3 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q3 2017 Total[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Oct-17[/TD]
[TD]Q4 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Nov-17[/TD]
[TD]Q4 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Dec-17[/TD]
[TD]Q4 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q4 2017 Total[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]450[/TD]
[/TR]
</tbody>[/TABLE]