# Creating formula to add data in fields based on date

#### VickieN

##### New Member
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?

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Just a 5-row/column sample would be helpful.

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.

VickieN said:
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?

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.

VickieN said:
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?

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.)

VickieN said:
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?

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.

VickieN said:
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
ABCDEFGH
1FriMonTueWedThu
228-Oct-0531-Oct-051-Nov-052-Nov-053-Nov-051-Oct-051-Nov-05
31,000176,24450,00052,23740,000177,244142,237
420,000176,24410,00020,000420,908196,244450,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.

Replies
3
Views
289
Replies
3
Views
59
Replies
0
Views
545
Replies
3
Views
121
Replies
6
Views
240

1,203,078
Messages
6,053,400
Members
444,661
Latest member
liamoohay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back