software

I need help with a formula to calculate the cumulative result

If cell a1 contains May, what formula will give me the cumulative
result 15 based on the data in the table belowMAY
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Cum

1 2 3 4 5 6 15--
Paul Sheppard------------------------------------------------------------------------
Paul Sheppard's Profile: www.excelforum.com/member.php...oamp;userid=24783
View this thread: www.excelforum.com/showthread...hreadid=503228
Hi Paul,

=SUM(OFFSET($A$4,0,0,1,HLOOKUP($A$1,$A$2:$L$3,2,FA LSE)))

Will work assuming that:
*the month to accumulate upto is in cell A1
*the months of the yr are headers in row 2,
*the number of the month is in row 3 (eg Jan = 1, Feb = 2 etc, this row
can be hidden). (Someone else maybe able to suggest a way of bypassing
the need for this extra row), amp;
*row 4 contains your data to sum.

This works by setting the size of the sum range by basing the amount of
columns to include on the month entered in A1.

To make it easier for users to vary the months to include in the total
I'd also create a dropdown list in cell A1. This can be done by
selecting A1 [data - validation - settings], selecting quot;listquot; for the
Allow box amp; typing quot;=$A$2:$L$2quot; (or selcting the cells that the months
are in using the mouse) into the Source field.

hth,
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=503228
Hi Rob

Thanks for this

If anybody knows a way to do it without the hidden row I'd be grateful

broro183 Wrote:
gt; Hi Paul,
gt;
gt; =SUM(OFFSET($A$4,0,0,1,HLOOKUP($A$1,$A$2:$L$3,2,FA LSE)))
gt;
gt; Will work assuming that:
gt; *the month to accumulate upto is in cell A1
gt; *the months of the yr are headers in row 2,
gt; *the number of the month is in row 3 (eg Jan = 1, Feb = 2 etc, this row
gt; can be hidden). (Someone else maybe able to suggest a way of bypassing
gt; the need for this extra row), amp;
gt; *row 4 contains your data to sum.
gt;
gt; This works by setting the size of the sum range by basing the amount of
gt; columns to include on the month entered in A1.
gt;
gt; To make it easier for users to vary the months to include in the total
gt; I'd also create a dropdown list in cell A1. This can be done by
gt; selecting A1 [data - validation - settings], selecting quot;listquot; for the
gt; Allow box amp; typing quot;=$A$2:$L$2quot; (or selcting the cells that the months
gt; are in using the mouse) into the Source field.
gt;
gt; hth,
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...--
Paul Sheppard------------------------------------------------------------------------
Paul Sheppard's Profile: www.excelforum.com/member.php...oamp;userid=24783
View this thread: www.excelforum.com/showthread...hreadid=503228
Hi Paul,
I had another look amp; using the same assumptions as before except for
the need for a hidden row, the following will work;

=SUM(OFFSET($A$5,0,0,1,MATCH($A$1,$A$3:$L$3,0)))

Hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...
Paul Sheppard Wrote:
gt; Hi Rob
gt;
gt; Thanks for this
gt;
gt; If anybody knows a way to do it without the hidden row I'd be grateful--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=503228
Hi Rob

Thanks for this you are a star

broro183 Wrote:
gt; Hi Paul,
gt; I had another look amp; using the same assumptions as before except for
gt; the need for a hidden row, the following will work;
gt;
gt; =SUM(OFFSET($A$5,0,0,1,MATCH($A$1,$A$3:$L$3,0)))
gt;
gt; Hth
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...--
Paul Sheppard------------------------------------------------------------------------
Paul Sheppard's Profile: www.excelforum.com/member.php...oamp;userid=24783
View this thread: www.excelforum.com/showthread...hreadid=503228
Hi Paul,
No problem, thanks for the feedback.

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=503228