Creating formula to add data in fields based on date

[VickieN]

I have a spreadsheet that forecasts cash and is updated weekly by other users for the upcoming 12 weeks and then sent to me. Each column heading is a day of the week and the dates change as new weekly reports are submitted. I need a calculation in the report template that will automatically add the data in the fields based on dates. As an example, if the date of the report was 11/14/05 - 2/3/06, I would need to add all the data in the fields associated with the November dates, and then add the data in the fields associated with the December dates, and so on. Can anyone help with a formula or macro to do this?

 

[Aladin Akyurek]

Just a 5-row/column sample would be helpful.

 

[VickieN]

Here is an example:

28-Oct-05 31-Oct-05 01-Nov-05 02-Nov-05 03-Nov-05
Friday Monday Tuesday Wednesday Thursday


1,000 176,244 50,000 52,237 40,000


20,000 176,244 10,000 20,000 420,908


In this example, I would need the total of each row for October (177,244 for row 1, and 196,244 for row 2), and the totals for November (142,237 for row 1 and 450,908 for row 2). A normal spreadsheet would be 60 columns of info (12 weeks * 5 days) and would roll forward weekly to add a new 12th week and drop off the previous 1st week. I hope this helps! Thank you.

 

[Aladin Akyurek]

Here is an example:

28-Oct-05 31-Oct-05 01-Nov-05 02-Nov-05 03-Nov-05
Friday Monday Tuesday Wednesday Thursday


1,000 176,244 50,000 52,237 40,000


20,000 176,244 10,000 20,000 420,908


In this example, I would need the total of each row for October (177,244 for row 1, and 196,244 for row 2), and the totals for November (142,237 for row 1 and 450,908 for row 2). A normal spreadsheet would be 60 columns of info (12 weeks * 5 days) and would roll forward weekly to add a new 12th week and drop off the previous 1st week. I hope this helps! Thank you.

How do you specify the date criteria?

 

[VickieN]

Well, I'm not sure I understand your question, but the cells are formatted in a date format. The user inputs the new starting date and would type 11/1/05 (as an example), the remaining dates are calculated. The calculated cells add 1 (for Tuesday through Friday) or 3 (to skip the weekend and get a Monday date) to the previous cell to get the next date.

If I'm missing your point, let me know and I'll try again. Thank you.

 

[Aladin Akyurek]

Well, I'm not sure I understand your question, but the cells are formatted in a date format. The user inputs the new starting date and would type 11/1/05 (as an example), the remaining dates are calculated. The calculated cells add 1 (for Tuesday through Friday) or 3 (to skip the weekend and get a Monday date) to the previous cell to get the next date.

If I'm missing your point, let me know and I'll try again. Thank you.

Do you have somewhere date criteria the formula can refer to in order to sum only those values which are related to the criteria?

 

[VickieN]

I don't think so. The worksheet is just rolling forward a 12 week period, so cells B4 - BJ4 are dates. B4 is the cell the user enters the new starting date of the forecast each week. The remaining cells are calculated. (If user inputs a date in cell B4 of 11/7/05, Cell B5 contains a formula "=B4+1" and so on throughout the worksheet.)

 

[Aladin Akyurek]

I don't think so. The worksheet is just rolling forward a 12 week period, so cells B4 - BJ4 are dates. B4 is the cell the user enters the new starting date of the forecast each week. The remaining cells are calculated. (If user inputs a date in cell B4 of 11/7/05, Cell B5 contains a formula "=B4+1" and so on throughout the worksheet.)

I'm failing to ask the right question...

Your sample shows 5 dates and 2 rows of worth data.

Must the formula sum all values in Oct-05 it comes across per row and separately all values in Nov-05 it comes across per row?

 

[VickieN]

Yes - each row would need to total by month. (I've got about 15 rows of data. There is different data on each row so the sums of the rows will not be combined, but each row would need to sum by month.)
thank you.

 

[Aladin Akyurek]

Yes - each row would need to total by month. (I've got about 15 rows of data. There is different data on each row so the sums of the rows will not be combined, but each row would need to sum by month.)
thank you.

Book5
	A	B	C	D	E	F	G	H
1	Fri	Mon	Tue	Wed	Thu
2	28-Oct-05	31-Oct-05	1-Nov-05	2-Nov-05	3-Nov-05		1-Oct-05	1-Nov-05
3	1,000	176,244	50,000	52,237	40,000		177,244	142,237
4	20,000	176,244	10,000	20,000	420,908		196,244	450,908
5
Sheet1

G3, copied across then down:

=SUMPRODUCT(--($A$2:$E$2-DAY($A$2:$E$2)+1=G$2),$A3:$E3)

Note that the dates in G2:H2 are the first day dates of the month/year pairs of interest.