Summing every nth row value based upon variable

[Lram]

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
"Month" in a cell that represents "year-to-date-thru".

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

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

Thanks,

Lram

 

[Bob Phillips]

Try 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)

 

[Guest]

Currently, I believe Excel nests to 7 levels

 

[Mikeopolo]

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("A3:"&M1&"3"))

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

 

[Bob Phillips]

Apart 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)