ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Adding Progressive Months Totals (https://www.excelbanter.com/excel-worksheet-functions/242992-adding-progressive-months-totals.html)

badboy
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

Jacob Skaria
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

Pete_UK
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

badboy
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

badboy
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

badboy
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


All times are GMT +1. The time now is 02:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com