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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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)

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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.

762 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