Change sum range automatically

[Spence]

Hello,

I have zero knowledge about macros or even where to start with them, so I am hoping that this problem can be solved within the set of available Excel functions.

I have 300 sales reps. Each one has been in there position for a different length of time. I want to sum there monthly revenue based on how long they have been in their position. I am currently using nested "if" statements, but after 7 months, I am hooped! Here is what I am using:

=IF(T4=6,SUM(W4:AB4),IF(T4=5,SUM(X4:AB4),IF(T4=4,SUM(Y4:AB4),IF(T4=3,SUM(Z4:AB4),IF(T4=2,SUM(AA4:AB4),IF(T4=1,AB4,0))))))

Each month I add a new column of revenue, so the range increases.

Does anyone know a way to set the sum range for each individual rep? I have a figure calculated for the number of months of revenue that should be summed, if that's any help.

 

[Brianwarnock]

I'm not sure that I understand how things are going to change but you might like to explore the use of Offset.
then if the start range is in say A4 and B4 contains the number of months


Sum(offset(a4,0,0)ffset(a4,0,b4)) would sum the number of columns, however you requirement is more complex,
so let a1 be the last month
b1 is the total number of months
c1 is the number of months for the rep
d1 =(b1-c1-1)*-1 this gives the offset for the start for the rep
sum(offset(indirect(a1),0,d1),indirect(a1))
will be the formula you require
the use of the Indirect function is so that each month you change a1 and b1 only , plus i suppose all of the reps permitted months?

perhaps I've tackled it incorrectly and we should store their starting month.

Got to go now but that should give you something to chew on.

Brian

 

[Spence]

Thanks Brian! I will let you know how it works out.

 

[Brianwarnock]

I'm curious to know what data is actually held regarding the reps if for example we had a start month or date for the rep and a start date/month for the stats then the start col for each rep could be calculated and would be fixed making things much simpler.
The Month option is only valid if the stats are for one year only, else the dates are required.

Brian