software

In a Budget/Actual spreadsheet I would like to be able to sum the
monthly budget values in a year-to-date cell that corresponds to the
number of months of the year that have elapsed. I use a Name value
quot;Monthquot; in a cell that represents quot;year-to-date-thruquot;.

I have tried to accomplish this by using a nested IF statement the
matches the quot;Monthquot; with a string of ( ) to sum the values from the
appropriate cells, i.e., F12 I12 L12 for the quot;Marquot; entry. This
obviously creates a very long nested IF which seems to fail if I go
beyond the 9th nest or quot;Sepquot;.

Is there a better way to do this? Is there an upper limit on the
number of nested IFs?

Thanks,

Lram--
Lram------------------------------------------------------------------------
Lram's Profile: www.excelforum.com/member.php...nfoamp;userid=238
View this thread: www.excelforum.com/showthread...hreadid=528887Try this

=SUMPRODUCT(--(MOD(COLUMN(F12:O12),3)=0),F12:O12)

The 3 after the mod is where you would add your month number. Also extend
beyond O12 as far as is needed.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Lramquot; gt; wrote in message
...
gt;
gt; In a Budget/Actual spreadsheet I would like to be able to sum the
gt; monthly budget values in a year-to-date cell that corresponds to the
gt; number of months of the year that have elapsed. I use a Name value
gt; quot;Monthquot; in a cell that represents quot;year-to-date-thruquot;.
gt;
gt; I have tried to accomplish this by using a nested IF statement the
gt; matches the quot;Monthquot; with a string of ( ) to sum the values from the
gt; appropriate cells, i.e., F12 I12 L12 for the quot;Marquot; entry. This
gt; obviously creates a very long nested IF which seems to fail if I go
gt; beyond the 9th nest or quot;Sepquot;.
gt;
gt; Is there a better way to do this? Is there an upper limit on the
gt; number of nested IFs?
gt;
gt; Thanks,
gt;
gt; Lram
gt;
gt;
gt; --
gt; Lram
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Lram's Profile:
www.excelforum.com/member.php...nfoamp;userid=238
gt; View this thread: www.excelforum.com/showthread...hreadid=528887
gt;
Currently, I believe Excel nests to 7 levels

quot;Lramquot; wrote:

gt;
gt; In a Budget/Actual spreadsheet I would like to be able to sum the
gt; monthly budget values in a year-to-date cell that corresponds to the
gt; number of months of the year that have elapsed. I use a Name value
gt; quot;Monthquot; in a cell that represents quot;year-to-date-thruquot;.
gt;
gt; I have tried to accomplish this by using a nested IF statement the
gt; matches the quot;Monthquot; with a string of ( ) to sum the values from the
gt; appropriate cells, i.e., F12 I12 L12 for the quot;Marquot; entry. This
gt; obviously creates a very long nested IF which seems to fail if I go
gt; beyond the 9th nest or quot;Sepquot;.
gt;
gt; Is there a better way to do this? Is there an upper limit on the
gt; number of nested IFs?
gt;
gt; Thanks,
gt;
gt; Lram
gt;
gt;
gt; --
gt; Lram
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Lram's Profile: www.excelforum.com/member.php...nfoamp;userid=238
gt; View this thread: www.excelforum.com/showthread...hreadid=528887
gt;
gt;


Another way:

With my headings in row 2, and data in row 3, columns A to L, and a
column letter (a thru l) in cell M1, in cell M3, for the YTD total,
type:

=SUM(INDIRECT(quot;A3:quot;amp;M1amp;quot;3quot;))

This will resolve to =sum(A3:X3) where x is the letter you type in M1.I came up with this a long time before I learned about sumproduct,
which gives a more elegant solution.

Regards
Mike--
Mikeopolo
------------------------------------------------------------------------
Mikeopolo's Profile: www.excelforum.com/member.php...oamp;userid=18570
View this thread: www.excelforum.com/showthread...hreadid=528887Apart from the fact that this sums all values between say F12 and L12, where
the OP only wants every 3rd column (at least in the example), it does have
the limitation of being volatile. Maybe a problem, maybe not, but it is a
consideration.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Mikeopoloquot; gt; wrote
in message ...
gt;
gt; Another way:
gt;
gt; With my headings in row 2, and data in row 3, columns A to L, and a
gt; column letter (a thru l) in cell M1, in cell M3, for the YTD total,
gt; type:
gt;
gt; =SUM(INDIRECT(quot;A3:quot;amp;M1amp;quot;3quot;))
gt;
gt; This will resolve to =sum(A3:X3) where x is the letter you type in M1.
gt;
gt;
gt; I came up with this a long time before I learned about sumproduct,
gt; which gives a more elegant solution.
gt;
gt; Regards
gt; Mike
gt;
gt;
gt; --
gt; Mikeopolo
gt; ------------------------------------------------------------------------
gt; Mikeopolo's Profile:
www.excelforum.com/member.php...oamp;userid=18570
gt; View this thread: www.excelforum.com/showthread...hreadid=528887
gt;