Summing Every 2nd Column

[Hayton]

Hi Everyone, I have a situation where we have a 12 month budget. For each month we have a column for a unit budget and a column for a dollar budget. We repeat this for 12 months, the result is a table with table months of data.

I need a way to sum every second column (Dollars)in the following way. If we are 4 months into the budget it must sum 4 months worth of dollars, if we are 6 months into the budget then six months worth of dollars, etc. I need this to calualte automatically as we roll from one month to the next.

Any suggestions would be appreciated.

Hayton McGregor

 

[000Steve000]

You could use a bunch of nested If, THEN, ELSEs.

eg
=IF(W12>0,SUM(A12,C12,E12,G12,I12,K12,M12,O12,Q12,S12,U12,W12),
IF(U12>0,SUM(A12,C12,E12,G12,I12,K12,M12,O12,Q12,S12,U12),
If(s12>0,...............))) etc etc

 

[Hayton]

Hi Steve, I have thought of this solution but have been told that you can only nest 7 times. Need to test this. I think that the answer would be to do VBA function. I need to nest to 12 levels.

Hayton McGregor

 

[GlennUK]

Here's a starting point for you, assuming your units are in columns A C E G etc, and your dollar amounts are in columns B D F H J etc, then this formula will do it ...

=SUMPRODUCT(OFFSET(A4,0,0,1,months*2)*MOD(COLUMN(OFFSET(A4,0,1,1,months*2)),2))

This is performing the summing on row 4, and refers to a named cell containing the number of months to perform the summing for ( name of "months" ).



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.