Column(), Row(), offset? Help!

[p3psi]

I'm trying to create a formula that displays Quarterly earnings forcasts for multiple product lines by adding 3x monthly cells at a time. Here's a table to help understand example of what I'm trying to do:

Month	1	2	3	4	5	6	7	8	9	10	11	12
Sales	100	200	300	400	500	600	700	800	900	1000	1100	1200

Quarter	1	2	3	4
Sales Totals	SUM(Month1:M3)	SUM(M4:M5)	?????	?????

I'm repeating this calculation for multiple years and multiple product lines, so I want to create a formula that I can drag from the 1st or 2nd quarter sales totals field over to the remaining quarters, but I can't seem to figure out the "skip three columns" piece. What formula can I put into the cell for Q2 Sales Totals that can be drag-copied over to Q3 & Q4 Sales Totals (and subsequent years for my IRL spreadsheet) in order to make completing this task easily possible?

Thank you in advance.

[Pete_UK]

Assume your sales values are in B2:M2, and your Quarters are in B5:E5. Then you could put this in B6:

=INDEX($B$3:$M$3,(B5-1)*3+1)+INDEX($B$3:$M$3,(B5-1)*3+2)+INDEX($B$3:$M$3,(B5-1)*3+3)

Then you could copy this into C6:E6

Hope this helps.

Pete

[p3psi]

Great, thank you! The only change I would mention is we need to assume our sales values are B3:M3 for this particular formula to work, but the gist is exactly what I was looking for.

Thanks again.

[p3psi]

Quick question as a followup: Is there any way to calculate this without including the quarter value? What I would like to have:

Quarter 1 2 3 4 | 1 2 3 4 | 1 2 3 4 | 1 2 3 4

What I need to have in order for the formula to work:

Quarter 1 2 3 4 | 5 6 7 8 | 9 10 11 12 | 13 14 15 16

It's no big deal if not; I can just create a hidden row with the values of 1:16. Thanks again!

[Pete_UK]

I need to know exactly what cell references you are using to come up with a suitable formula - can you post an example workbook which uses the same layout as your real one? You can change any sensitive data before posting.

Pete

[jason.b75]

See if this helps.

p3psi example.xlsx

[p3psi]

Works perfectly. Thank you!

[jason.b75]

Another method that came to mind

=SUM(CHOOSE(COLUMNS($B5:B5),$B2:$D2,$E2:$G2,$H2:$J2,$K2:$M2))