| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Progressive Months Totals
Row 3 Column B Apr-09
Row 5 Column B 17,293 Row 3 Column C May-09 Row 5 Column c 16,849 Row 3 Column D Jun-09 Row 5 Column D 17,364 Row 3 Column E Jul-09 Row 5 Column E 24,066 Row 3 Column F Aug-09 Row 5 Column F 17,151 Row 3 Column G Sep-09 Row 5 Column G 17,151 Row 3 Column H Oct-09 Row 5 Column H 17,151 Row 3 Column I Nov-09 Row 5 Column I 16,372 Row 3 Column J Dec-09 Row 5 Column J 17,940 Row 3 Column K Jan-10 Row 5 Column K 16,440 Row 3 Column L Feb-10 Row 5 Column L 15,666 Row 3 Column M Mar-10 Row 5 Column M 28,663 Row 3 Column N Year To Date Totals Row 5 Column N 92,723 I am looking for a formula to place in Row 5 Column N, that will add the numbers from all months before the our current month (September) starting with April. Next month, I wish it to add April through September. The following month add April through October and so on. Each month it will add all months before the month we are currently in. I would be so darn happy if this can be done. Thank you in advance. -Bad |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding Progressive Months Totals
One way
Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(INDIRECT("B5:" & CELL("address",OFFSET(B5,0,MATCH(MONTH(TODAY()),MO NTH(B3:M3),0)-2)))) If this post helps click Yes --------------- Jacob Skaria "BadBoy" wrote: Row 3 Column B Apr-09 Row 5 Column B 17,293 Row 3 Column C May-09 Row 5 Column c 16,849 Row 3 Column D Jun-09 Row 5 Column D 17,364 Row 3 Column E Jul-09 Row 5 Column E 24,066 Row 3 Column F Aug-09 Row 5 Column F 17,151 Row 3 Column G Sep-09 Row 5 Column G 17,151 Row 3 Column H Oct-09 Row 5 Column H 17,151 Row 3 Column I Nov-09 Row 5 Column I 16,372 Row 3 Column J Dec-09 Row 5 Column J 17,940 Row 3 Column K Jan-10 Row 5 Column K 16,440 Row 3 Column L Feb-10 Row 5 Column L 15,666 Row 3 Column M Mar-10 Row 5 Column M 28,663 Row 3 Column N Year To Date Totals Row 5 Column N 92,723 I am looking for a formula to place in Row 5 Column N, that will add the numbers from all months before the our current month (September) starting with April. Next month, I wish it to add April through September. The following month add April through October and so on. Each month it will add all months before the month we are currently in. I would be so darn happy if this can be done. Thank you in advance. -Bad |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding Progressive Months Totals
Thank you so much! You have done it!
Well done. May I ask though, my total shows 301,369.43 but it should actually read 301,370 Can this be fixed? "Jacob Skaria" wrote: One way Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(INDIRECT("B5:" & CELL("address",OFFSET(B5,0,MATCH(MONTH(TODAY()),MO NTH(B3:M3),0)-2)))) If this post helps click Yes --------------- Jacob Skaria "BadBoy" wrote: Row 3 Column B Apr-09 Row 5 Column B 17,293 Row 3 Column C May-09 Row 5 Column c 16,849 Row 3 Column D Jun-09 Row 5 Column D 17,364 Row 3 Column E Jul-09 Row 5 Column E 24,066 Row 3 Column F Aug-09 Row 5 Column F 17,151 Row 3 Column G Sep-09 Row 5 Column G 17,151 Row 3 Column H Oct-09 Row 5 Column H 17,151 Row 3 Column I Nov-09 Row 5 Column I 16,372 Row 3 Column J Dec-09 Row 5 Column J 17,940 Row 3 Column K Jan-10 Row 5 Column K 16,440 Row 3 Column L Feb-10 Row 5 Column L 15,666 Row 3 Column M Mar-10 Row 5 Column M 28,663 Row 3 Column N Year To Date Totals Row 5 Column N 92,723 I am looking for a formula to place in Row 5 Column N, that will add the numbers from all months before the our current month (September) starting with April. Next month, I wish it to add April through September. The following month add April through October and so on. Each month it will add all months before the month we are currently in. I would be so darn happy if this can be done. Thank you in advance. -Bad |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding Progressive Months Totals
Ok all good now and thank you both again
"Jacob Skaria" wrote: One way Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(INDIRECT("B5:" & CELL("address",OFFSET(B5,0,MATCH(MONTH(TODAY()),MO NTH(B3:M3),0)-2)))) If this post helps click Yes --------------- Jacob Skaria "BadBoy" wrote: Row 3 Column B Apr-09 Row 5 Column B 17,293 Row 3 Column C May-09 Row 5 Column c 16,849 Row 3 Column D Jun-09 Row 5 Column D 17,364 Row 3 Column E Jul-09 Row 5 Column E 24,066 Row 3 Column F Aug-09 Row 5 Column F 17,151 Row 3 Column G Sep-09 Row 5 Column G 17,151 Row 3 Column H Oct-09 Row 5 Column H 17,151 Row 3 Column I Nov-09 Row 5 Column I 16,372 Row 3 Column J Dec-09 Row 5 Column J 17,940 Row 3 Column K Jan-10 Row 5 Column K 16,440 Row 3 Column L Feb-10 Row 5 Column L 15,666 Row 3 Column M Mar-10 Row 5 Column M 28,663 Row 3 Column N Year To Date Totals Row 5 Column N 92,723 I am looking for a formula to place in Row 5 Column N, that will add the numbers from all months before the our current month (September) starting with April. Next month, I wish it to add April through September. The following month add April through October and so on. Each month it will add all months before the month we are currently in. I would be so darn happy if this can be done. Thank you in advance. -Bad |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding Progressive Months Totals
Are those Excel dates on row 3, formatted to mmm-yy, or are they text
strings? If they are dates then you could use a formula like this in N5: =SUMPRODUCT((MONTH(B3:M3)<MONTH(TODAY()))*(YEAR(B3 :M3)=YEAR(TODAY ())),B5:M5) Hope this helps. Pete On Sep 17, 4:41*pm, BadBoy wrote: Row 3 Column B Apr-09 Row 5 Column B 17,293 Row 3 Column C May-09 Row 5 Column c 16,849 Row 3 Column D Jun-09 Row 5 Column D 17,364 Row 3 Column E Jul-09 Row 5 Column E 24,066 Row 3 Column F Aug-09 Row 5 Column F 17,151 Row 3 Column G Sep-09 Row 5 Column G 17,151 Row 3 Column H Oct-09 Row 5 Column H 17,151 Row 3 Column I Nov-09 Row 5 Column I 16,372 Row 3 Column J Dec-09 Row 5 Column J 17,940 Row 3 Column K Jan-10 Row 5 Column K 16,440 Row 3 Column L Feb-10 Row 5 Column L 15,666 Row 3 Column M Mar-10 Row 5 Column M 28,663 Row 3 Column N Year To Date Totals Row 5 Column N 92,723 I am looking for a formula to place in Row 5 Column N, that will add the numbers from all months before the our current month (September) starting with April. Next month, I wish it to add April through September. The following month add April through October and so on. Each month it will add all months before the month we are currently in. I would be so darn happy if this can be done. Thank you in advance. -Bad |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding Progressive Months Totals
Thank you Pete :P
"Pete_UK" wrote: Are those Excel dates on row 3, formatted to mmm-yy, or are they text strings? If they are dates then you could use a formula like this in N5: =SUMPRODUCT((MONTH(B3:M3)<MONTH(TODAY()))*(YEAR(B3 :M3)=YEAR(TODAY ())),B5:M5) Hope this helps. Pete On Sep 17, 4:41 pm, BadBoy wrote: Row 3 Column B Apr-09 Row 5 Column B 17,293 Row 3 Column C May-09 Row 5 Column c 16,849 Row 3 Column D Jun-09 Row 5 Column D 17,364 Row 3 Column E Jul-09 Row 5 Column E 24,066 Row 3 Column F Aug-09 Row 5 Column F 17,151 Row 3 Column G Sep-09 Row 5 Column G 17,151 Row 3 Column H Oct-09 Row 5 Column H 17,151 Row 3 Column I Nov-09 Row 5 Column I 16,372 Row 3 Column J Dec-09 Row 5 Column J 17,940 Row 3 Column K Jan-10 Row 5 Column K 16,440 Row 3 Column L Feb-10 Row 5 Column L 15,666 Row 3 Column M Mar-10 Row 5 Column M 28,663 Row 3 Column N Year To Date Totals Row 5 Column N 92,723 I am looking for a formula to place in Row 5 Column N, that will add the numbers from all months before the our current month (September) starting with April. Next month, I wish it to add April through September. The following month add April through October and so on. Each month it will add all months before the month we are currently in. I would be so darn happy if this can be done. Thank you in advance. -Bad |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add monthly totals of months forward | Excel Worksheet Functions | |||
| Looking for certain months and summing the totals | Excel Worksheet Functions | |||
| Add Totals for Last 12 Months | Excel Discussion (Misc queries) | |||
| How can I subtotal my weekly totals by months? | Excel Worksheet Functions | |||
| Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
Hybrid Mode
