Fix Formula to total a row by month for a rolling 12 months

Hi All,

I started working on a spreadsheet awhile back and someone on this forum helped me to figure this out originally. Since then I have added a ton of extra components to the sheet including some formatting and it seems to have affected the way this formula works. I have attached the spreadsheet. What I am trying to accomplish is this:

On the sheet "Ideal Expirations" I want Column E to calculate the total amount of traffic for the corresponding month in Column A. It should total from Sheet "Monday Count" Row 20 by the month and years listed in row 1.

The trick is I only want the number totaled from the last complete month. So Since we are in September the number should be totaled from 2013 along with Oct-Dec. Months Jan -July should only total 2014 numbers since those months are complete. Also the formula needs to know that we will be continually adding new columns every week, so if there is a range set in the formula somehow it needs to continue on each week.

Like I said I had a working formula in there but since adding the to spreadsheet it doesn't seem to be working. I left the original formula in there in case it helps. Thanks in advance for any advice!

Originally Posted by Tsjallie

At this moment your formula is counting the wrong period. For instance for September 2014 it's calculating June 2014.
Besides that I noticed some invalid dates in row 1 of sheet Monday Count. They can cause invalid results.
In column E of sheet Ideal Expirations try this formula. It is the formula for the row for September.
It totals the traffic starting 1-10-2014 and ending 31-8-2014. It is missing 1-4 Oct '13 though since that's a broken week. Will see if that can be solved (probably).

Formula:

Please Login or Register  to view this content.

I appreciate your help here....been stressing this one. I'm not sure I follow what you are saying? September should only total Row 20 from Monday Count Columns EQ,ER,ES,ET and EU. These are the traffic numbers in September (based on row 1's date) from last year (2013). Once September 2014 is over it should change to totaling GQ,GR,GS,GT and GU (GU will be added this monday). I hope I am explaining this good enough. When I entered your formula into Ideal Expirations E10 I got 662 instead of 52 which is what I'm trying to get.

Not sure what you mean by invalid dates in row 1? Are they not formatted correctly? Can you give me an example so I can fix?

Thanks again for working on this!

Originally Posted by Tsjallie

Guess some synapses have not been operational at the time a read your posting
Completely misinterpreted.

Hope I'm doing better now. Attached the edited workbook. Comments on sheet Ideal Expirations.
The formula takes all columns of rows 1 and 20 in the sheet Monday count so added columns each is no problem.

About the invalid dates. I found these:
[B1] -> 11/8/210
[H1] -> 12/13/201
[I1] -> 20-12-2011 iso 20-12-2010
[AL1] -> 11-7-2011 missing
[AS1] -> 5-9-2011 missing
[CP1] -> 27-8-2012 missing
[CW1] -> 10/15/201
Some will affect the results. Maybe it's a good thing to use data validation in row 1.

This is closer but still doesn't seem to be calculating correctly. I added two new columns to the Monday count GT and GU with 9/29/2014 and 10/6/2014 each with 10 traffic. Then I forwarded my system date to 10/6/2014. After closing and reopening the file the Idea Expiration sheet did adjust the months correctly making Oct Current but the totals didn't change. Sept shows 14 shows 57 when it should have been 67. I then added columns for all of October and the first week in November with traffic numbers. Closed the file changed system date to Nov 4th and then the Ideal Expirations for Oct 14 came out to 0.

I am not sure if I am testing it wrong, or if the formula isn't working properly? I really appreciate your help on this though. I know its taken some time!

Originally Posted by Tsjallie

Don't know what's the matter with. Really thought I had the formula look at the entire row, but it only looked at column B to GS.
That explain all.
Looking at the entire with ('Monday Count'!1:1) however doesn't work. Don't know why. Probably the Year function can't handle that. Used 'Monday Count'!$B$1:$XFD$1 instead.
Attached the corrected sheet with the changes you made.
Also added a dummy date for testing purposes so you don't have to change your system date.

Thanks! It works. Unfortunately I am back to my original problem. I seem to be going in circles. Now the formulas on my Quotes Sheet don't work. Which is ironic because originally this sheet was working, and it stopped working when I got the Quotes Sheet working. I seem to be back to square one. I appreciate your help nonetheless and will mark it as solved. On to the next thing....again!

Originally Posted by Tsjallie

Glad I could help.
About the issue in the Quotes sheet: your using a formula in column D which looks up something in column D of the Ideal Expirations sheet using offset.
As the order of the table in Ideal Expirations has changes there may lie the problem.
Let me know if I can help you any further.

Well any more help would be greatly appreciated. I like your way of doing the Ideal Expirations better then how it was working previously but if I can't get the quotes to work I have to start back at square one. What I am trying to accomplish on the Quotes sheet is this:

I want the Quote sheet to only give rental rates for the lease terms we want. So when someone comes in whose lease is up in August and we enter the apartment number on the Quote Sheet I want the workbook to look at ideal expirations, see that we do not need any additional leases to expire in July and therefore put N/A in the 11 Month Rental Rate. Sp they will only get options for the months that we have room for leases.

A few notes: We always expire leases on the last day of the month, so day of the month does not come into play. Also 12 months will always be an option whether we need them in that month or not. In theory it could be where the sheet has all N/A's which we cannot have, so 12 months should always list a price.

Hope that makes sense. Thanks!

Originally Posted by Tsjallie

Hmm, I'll have to chew on that for a while
Get back to you.

OK I've been chewing on this for awhile myself and broke down the formula current contained in the Quote sheet. I learned a whole lot about MOD and OFFSET and so I am grateful for that. I've come to the conclusion though that the only way to get this to work (and hopefully the easiest) would be to alter your formula. I've been trying to figure your formulas out but haven't been too successful. So I am wondering if you can help me again. Is it possible to have your formula not move the months? So Jan always stays in Row 2 Feb in row 3 and so on and so forth. If I can get that to work then the original formula in the quotes sheet should work. I tried to do this a few time, but when I do I get all kinds of errors. I would appreciate your help once again if this is possible.

Thanks!

I was able to find the way to do this through another thread. Thanks so much again for the help. For anyone interested the thread is here:
http://www.excelforum.com/excel-form...ml#post3855473