Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number of items fit into item | Excel Worksheet Functions | |||
Number of items in a colum | Excel Worksheet Functions | |||
Number of items per day in chart. | Charts and Charting in Excel | |||
Count number of items by month | Excel Worksheet Functions | |||
Number of items meeting a > criterion | Excel Worksheet Functions |