Sum Date Based On Date

soccerdude44

New Member
Joined
Jun 10, 2015
Messages
9
I am trying to get my 1st sheet to be a summary of my other 5 sheets that I have within my workbook. Basically what I have done is created a spreadsheet for each type of investment account I have. Within each sheet I have put what I want my projected monthly contribution to be along with my expected average rate of return. This has given me an amount that I have as my goal that I want in each account at the end of each month. I then have a column for the actual amount I have at the end of each month. And then another column that shows the difference between the actual and goal amounts so I know how far ahead or behind I am. I have extended it down through the age of 65 on a month by month basis. What I would then like to do is have the current month difference be shown on the summary sheet (the first page) for all 5 accounts. So in the perfect world my summary sheet would have 5 different rows, one for each account, that shows the difference for each account but that can change which cell it is looking at for the difference based on what month it is without me having to manually update at the end of each month. Does that make sense?? Let me know if you have any questions.

Thanks For Your Help!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
Assume:
1. One sheet is named "Investment1" for one of your investment accounts.
2. Col A lists the last day of each month in Excel date format
3. Col D is the difference between actual and goal at the end of each month

Try this formula on one row of the summary sheet and then modify it by changing the sheet name to "Investment2", etc (or whatever your sheet names are):
Code:
=INDEX(Investment1!D:D,MATCH(EOMONTH(TODAY(),-1),Investment1!A:A,0))
 

soccerdude44

New Member
Joined
Jun 10, 2015
Messages
9
Your a genius!! That is exactly what I was looking for! Just out of curiosity did you know that off the top or your head??? If so how did you learn so much? I would love to become an Excel Wizard!!

Thank!!
 
Last edited:

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
You're welcome!

I learned Excel by using it for many years and using books & google to figure out approaches to solve problems. Searching this forum is also extremely helpful in learning new Excel techniques This Mr Excel website has many resources that can help. If you haven't done so already, try to understand the formula I gave by googling "Excel Index/Match" and "Excel eomonth" and "Excel today()". If you have questions, don't hesitate to ask.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,303
Messages
5,600,855
Members
414,407
Latest member
Zaner0445

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top