VBA to evaluate a cell value, and based on that value return a sum of a specific set of cells

[Seanm290]

I need a regular formula, or a macro that will evaluate a cell value (in my case, 1-12 representing fiscal periods), and based on that value, return a sum of specific cells. Is this best achieved using a macro or another formula type? Obviously, there will be 12 possible summations based on the cell value.

Thanks!

 

[keith101]

Possible formulae depend on how varied the different sums are. If there is a regular pattern you could try using the INDIRECT function to calculate the addresses to sum, or the OFFSET function. Another possibility is the CHOOSE function where you would list the twelve possible ranges. Hope that helps, but if you post more detail I could be more specific!

Keith

 

[Seanm290]

Below is an example of what I'm looking for. In this case, period is 4, so the result should be 590.00. I believe there is a limit on how many formulae can be nested, so I don't think that will work (will it?).

Period	4
Periods	1	2	3	4	5	6	7	8	9	10	11	12
	100.00	150.00	160.00	180.00	190.00	150.00	60.00	400.00	85.00	135.00	22.00	86.00
	IF(B3=1,(B7))
	IF(B3=2,(B7+C7))
	IF(B3=3,(B7+C7+D7))
	IF(B3=4,(B7+C7+D7+E7))
	……
	IF(B3=12,(B7+C7+D7+E7+F7+G7+H7+I7+J7+K7+L7+M7))

<tbody>
</tbody><colgroup><col span="13"></colgroup>

 

[keith101]

Try:

=SUM(B7:OFFSET(B7,,B3-1))

Keith

 

[Seanm290]

I'm not sure how or why, but that worked! I've never heard of that function before.

Many thanks!