Advanced Excel Question - is there a formula that automatically updates based on date?

Question

For instance- At the beginning of the week, I already have information in row 1, columns A - Z. On the 2nd workday, I would like my total to be the sum of A1. On the 3rd workday, I would like my total to be the sum of A1 + A2. On the 4th workday, I would like my total to be the sum of A1 + A2 + A3. Is this possible? This is a simplified explanation for a very complicated excel document, and a solution to this problem would save my day! Thank you in advance.

If it helps, I already have a cell in the document which automatically updates to the current date. However, I wonder if by looking to link to the date I might be on the wrong path?

http://i30.photobucket.com/albums/c326/Jennifer__B/SampleExcelWorksheet.jpg

Above is a link to a mock example spreadsheet. The column I am looking to fill is AJ5 - AJ26. I need to keep as close to this format as possible, as this is not my document to change. So, based on the information in the picture, on the 13th, I would like column AJ to sum days 1 thru 12. On the 14th I would like the column AJ to sum days 1 - 14. Is there a formula for this to automatically update without having to manually change the formula daily?

Answer 1

Yes you can do this. You will need corresponding column of dates to make it work.
In my example, I put values are in A2, A3, A4, etc to A34. I put dates in each corresponding cell B2, B3, B4, etc through B34. I left A1 and B1 as label cells with "Sales" and "Date" respectively.

Then in cell C2 I have =TODAY()

In Cell C3 is the following formula:

{=SUM (IF ($B$2:$B$34 <=C1, $A$2:$A$34, 0))}

You'll notice the brackets {} appear in first and last charcter. Those are inserted automatically when using conditional sum wizard. You can past the above formula into Excel but it won't work without the brackets. And the only way to get those bracket is to use conditional sum wizard.

How?

Hightlight the data range including labels. Then click Tools and choose Wizard, Conditional Sum. Follow the window steps and you will produce the correct formula on your own. Its that easy!

Hope this helps,

Kind regards,
QwertyKPH @ Yahoo!

Answer 2

You can sum days that have no values since they won't add anything to your total. I think you mean that you only want a running total for the month. What I would do is insert a row at the top and set it to be:

C1=MONTH(C5)
D1=MONTH(D5)
...
etc.
Assuming you have currently have row 4 with dates, not just day numbers.

Then I'd set the cells in AJ to be:
=SUMIF(C$1:AW$1, MONTH ($A$2),C6:AE6)
Starting at AJ6 and copying down.
assuming that A2 is the cell with the current date.

Answer 3

I know there is, but I didn't do too well in this formula section on Excel..but hey, I did make a "B" for the whole semester!!!!!!

Answer 4

make the value of the Age Cell as "=(YEAR(TODAY())-YEAR(C3))" here C3 is the Value of DOB Cell and use this without Quotes.

Answer 5

Yes, there is a formula.