Sum a different number of items
 

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.

Sum a different number of items
 

=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" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.

Sum a different number of items
 

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

--
Biff
Microsoft Excel MVP


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.

Sum a different number of items
 

Thanks Bob.

As always, you're a life saver!

"Bob Phillips" wrote:

=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" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.

Sum a different number of items
 

Yep.

That looks like it would do it too.

Thanks!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.

Sum a different number of items
 

You're welcome!

--
Biff
Microsoft Excel MVP


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Yep.

That looks like it would do it too.

Thanks!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.