Adding up various columns based on the value of one changing cell
 

I am trying to figure out the best way to calculate a year to date total in a
particular spreadsheet. Here is an example of what it looks like

Number of months 4
1 2 3 4 5 6
a 100 100 100 100 100 100
b 100 100 100 100 100 100
c 100 100 100 100 100 100
d 100 100 100 100 100 100
e 100 100 100 100 100 100
f 100 100 100 100 100 100
g 100 100 100 100 100 100

So what I need to do is find a formula that will go across each row and only
up the cells that are equal or less than the number of months number that has
been put in. So say for instance I want four months of data then each row
should bring back a value of 400 (100 for 1, 100 for 2, etc...) Please tell
me this is possible, it seems like it should be very simple but I can't
figure it out!!! Thank you for any help you can offer!

Adding up various columns based on the value of one changing cell
 

=SUM(OFFSET($B3,,,,$C$1))

C1=Number of months

Data starts in Column B (in my example row 3)

"Darrilyn" wrote:

I am trying to figure out the best way to calculate a year to date total in a
particular spreadsheet. Here is an example of what it looks like

Number of months 4
1 2 3 4 5 6
a 100 100 100 100 100 100
b 100 100 100 100 100 100
c 100 100 100 100 100 100
d 100 100 100 100 100 100
e 100 100 100 100 100 100
f 100 100 100 100 100 100
g 100 100 100 100 100 100

So what I need to do is find a formula that will go across each row and only
up the cells that are equal or less than the number of months number that has
been put in. So say for instance I want four months of data then each row
should bring back a value of 400 (100 for 1, 100 for 2, etc...) Please tell
me this is possible, it seems like it should be very simple but I can't
figure it out!!! Thank you for any help you can offer!

Adding up various columns based on the value of one changing c
 

Thank you so much!!!! One more question. If there anyway to get it to add
only certain columns? In my spreadsheet all of the columns that I need to
add up are not all next to each other, there are additional columns in
between. The way the spreadsheet is set up it has 4 columns for each month
(budget, actual, variance, % variance). I just want to add up the budget
columns for each month so it will have to skip all the other columns. I hope
that makes sense. Thank you again, you are brilliant!!!!

"Toppers" wrote:


=SUM(OFFSET($B3,,,,$C$1))

C1=Number of months

Data starts in Column B (in my example row 3)

"Darrilyn" wrote:

I am trying to figure out the best way to calculate a year to date total in a
particular spreadsheet. Here is an example of what it looks like

Number of months 4
1 2 3 4 5 6
a 100 100 100 100 100 100
b 100 100 100 100 100 100
c 100 100 100 100 100 100
d 100 100 100 100 100 100
e 100 100 100 100 100 100
f 100 100 100 100 100 100
g 100 100 100 100 100 100

So what I need to do is find a formula that will go across each row and only
up the cells that are equal or less than the number of months number that has
been put in. So say for instance I want four months of data then each row
should bring back a value of 400 (100 for 1, 100 for 2, etc...) Please tell
me this is possible, it seems like it should be very simple but I can't
figure it out!!! Thank you for any help you can offer!

Adding up various columns based on the value of one changing c
 

try:

=SUM(OFFSET($B3,0,MOD(COLUMNS($B3:$IV3)-1,4)=0,1,$C$1*4))

"Darrilyn" wrote:

Thank you so much!!!! One more question. If there anyway to get it to add
only certain columns? In my spreadsheet all of the columns that I need to
add up are not all next to each other, there are additional columns in
between. The way the spreadsheet is set up it has 4 columns for each month
(budget, actual, variance, % variance). I just want to add up the budget
columns for each month so it will have to skip all the other columns. I hope
that makes sense. Thank you again, you are brilliant!!!!

"Toppers" wrote:


=SUM(OFFSET($B3,,,,$C$1))

C1=Number of months

Data starts in Column B (in my example row 3)

"Darrilyn" wrote:

I am trying to figure out the best way to calculate a year to date total in a
particular spreadsheet. Here is an example of what it looks like

Number of months 4
1 2 3 4 5 6
a 100 100 100 100 100 100
b 100 100 100 100 100 100
c 100 100 100 100 100 100
d 100 100 100 100 100 100
e 100 100 100 100 100 100
f 100 100 100 100 100 100
g 100 100 100 100 100 100

So what I need to do is find a formula that will go across each row and only
up the cells that are equal or less than the number of months number that has
been put in. So say for instance I want four months of data then each row
should bring back a value of 400 (100 for 1, 100 for 2, etc...) Please tell
me this is possible, it seems like it should be very simple but I can't
figure it out!!! Thank you for any help you can offer!

Adding up various columns based on the value of one changing c
 

My last answer was utter rubbish!

try:

=SUM(IF(MOD(COLUMN(INDIRECT("$B" & ROW() &":" &
ADDRESS(ROW(),$C$1*4-2,2)))-2,4)=0,INDIRECT("$B" & ROW() &":" &
ADDRESS(ROW(),$C$1*4-2,2))))

Enter with Ctrl+Shift+Enter

"Toppers" wrote:

try:

=SUM(OFFSET($B3,0,MOD(COLUMNS($B3:$IV3)-1,4)=0,1,$C$1*4))

"Darrilyn" wrote:

Thank you so much!!!! One more question. If there anyway to get it to add
only certain columns? In my spreadsheet all of the columns that I need to
add up are not all next to each other, there are additional columns in
between. The way the spreadsheet is set up it has 4 columns for each month
(budget, actual, variance, % variance). I just want to add up the budget
columns for each month so it will have to skip all the other columns. I hope
that makes sense. Thank you again, you are brilliant!!!!

"Toppers" wrote:


=SUM(OFFSET($B3,,,,$C$1))

C1=Number of months

Data starts in Column B (in my example row 3)

"Darrilyn" wrote:

I am trying to figure out the best way to calculate a year to date total in a
particular spreadsheet. Here is an example of what it looks like

Number of months 4
1 2 3 4 5 6
a 100 100 100 100 100 100
b 100 100 100 100 100 100
c 100 100 100 100 100 100
d 100 100 100 100 100 100
e 100 100 100 100 100 100
f 100 100 100 100 100 100
g 100 100 100 100 100 100

So what I need to do is find a formula that will go across each row and only
up the cells that are equal or less than the number of months number that has
been put in. So say for instance I want four months of data then each row
should bring back a value of 400 (100 for 1, 100 for 2, etc...) Please tell
me this is possible, it seems like it should be very simple but I can't
figure it out!!! Thank you for any help you can offer!