Sum a different number of items

[mr tom]

Hi,

Another knotty one which has been puzzling me.

I've got a list of salesmen by their ID, with various information, including
their targets to the right.

Let's say the monthly targets begin in the 9th column (Jan) and each
additional column is another month (i.e. 10th is Feb etc) there are then more
occupied columns after this.

Their targets are seasonally adjusted, so each month is unique.

I can pick out their current month target by returning the value in column
8+(this month) without any trouble.

I also need to know their year to date targets, so for april, this would be
the value in column (8+4, April) plus the value in (8+3, March), plus the
value in (8+2, Feb), plus the value in (8+1, Jan). Obviously with each
following month, another column (8+5 for May) gets added to the list.

Anybody got a sensible idea for doing this automatically by formula?

Perhaps something along the lines of SUM(B9:xxx9) where the column letter
for xxx is calculated automatically given 8+the number of the current month.

Any bright ideas?

Cheers,

Tom.

 

[Bob Phillips]

=SUM(I6:INDEX(I6:T6,1,MONTH(TODAY())))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

*Maybe* something like this...

Assuming you have 12 columns for monthly data. You enter this data starting
in B9 and each month add data to the columns to the right in a contiguous
manner so that at the 12th month the range would be B9:M9.

=SUM(B9:INDEX(B9:M9,COUNT(B9:M9)))

 

[mr tom]

Thanks Bob.

As always, you're a life saver!

=SUM(I6:INDEX(I6:T6,1,MONTH(TODAY())))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mr tom]

Yep.

That looks like it would do it too.

Thanks!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Yep.

That looks like it would do it too.

Thanks!