Excel YTD total

Posted on 2003-02-27
Medium Priority
Last Modified: 2011-09-20
I have a spreadsheet that has 26 columns with a weeking ending date at the top of each.

In the rows below I have data that is pulled in by query on a weekly basis. I have a total column before all these columns that I keep a running sum total of values.  I think there is an easier way to update that total column.  Right now, I find and replace the column value in my sum formula and increment it to the new column.

Can I update based on the today() to change the formula?

Any ideas?

Sample data:

Total 1/4  1/11  1/18  1/25  2/1  2/8  2/15  2/22  etc

50    10    5     5     20    0    0    10
60    10    10    10    10    0    10   10

Question by:Grubeaters
LVL 16

Expert Comment

ID: 8039675
Hi Grubeaters,

Say 'Total is in A1 ie totals in A2, A3, A4
A2 is the sum of B2, C2 , D2..... up to AA2

To sum up in A2, enter:
  =SUM( B2:AA2 )
You can also add up the whole row after A2 (blank cells are interpreted as zeroes) ie A2:

Not sure i understand the problem correctly though.


Expert Comment

ID: 8043409

I also agree with sebastienm.  Simply have your sum equation in cell A1 with the equation reading

Each week when a new number is added to your spreadsheet the total should include the addition.

Let me know if this helps.
LVL 16

Expert Comment

ID: 8043544
actually, pjmcgovern, the ZZ column doesn't exist.It goes up to 256 columns ie IV (iv). (except for the Excel Web Component Sheet)

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 8043883
I'm sorry that I didn't explain completely. There are three sections (this year, last year, and two years ago) which are stacked as described earlier so my first column which is YTD needs to change weekly as the current week changes so we can compare this year's YTD with last year's YTD and YTD two years ago. If I use =sum(b2:az2), it will not show proper comparisons for YTD versus prior years.

LVL 16

Accepted Solution

sebastienm earned 150 total points
ID: 8044074
hmmm .. i still don't get it :-)
Are the years on 1 row?

... However:

Say your dates are in B1:AZ1 whatever... but on row 2.
=MATCH(TODAY(),$B$1:$AZ$1,1)   returns the index of the closest date to Today() (and lower than Today) in $B$1:$AZ$1
Eg. in your first post example, today being the 28th of Feb, the formula would find the match for 2/22, therefore it would return its index: 8
Say this returned number is j

Now, with a similar formula, you could find the index for the first date of the year... say i

now , to add up only these cells between i and j in row 2, use something like
ie sum the following range:
   shift A1 of i columns and 0 rows, then resize the cell with 1 row and (j-i+1) columns.
(replace i,j in the Offset formula by the appropriate match formulas)

I hope this helps.
LVL 11

Expert Comment

ID: 8044134
You could easily do this with VBA... but I'm fairly unclear on your setup.

There are a number of ways you could approach this, but off the top of my head, I would say that you could qualify a variable, using today's date to find the last completed weekend, then use that column as a reference to edit your calcs.

I.e. todays date = 2/28 which means the last completed weekend was 2/22. So it figures out which column 2/22 is located in (column H) and edits your calcs to end with that column.

I can give you an example, if this is what you're looking for, but like I said, I'm quite unsure of exactly what you're trying to accomplish, so just let me know.


Author Comment

ID: 8059554
Based on the answers given, I again apologize for not explaining my problem clearly enough.  I want thank everyone who posted a suggestion though.  Sebastien's suggestion was the closest comment to what I was looking for to solve my immediate need although I agree with the last person that this situation could have been resolved easily with VBA.  

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
I came across an unsolved Outlook issue and here is my solution.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question