Break down date ranges

[Legacy 351788]

Hello,

From the below I have in cell A1:A31 dates. B1:B31 DOW and C1:C31 number of rooms sold. I am wanting a formula that firstly breaks the month down into weeks that run Fri-Thurs and then calculates the number of rooms sold during that week.

This will be done every month and so the DOW will change reflective to how they fall in the month and so the formula will need to account for this.

Is this possible?

01/10/2015

02/10/2015

03/10/2015

04/10/2015

05/10/2015

06/10/2015

07/10/2015

08/10/2015

09/10/2015

10/10/2015

11/10/2015

12/10/2015

13/10/2015

14/10/2015

15/10/2015

16/10/2015

17/10/2015

18/10/2015

19/10/2015

20/10/2015

21/10/2015

22/10/2015

23/10/2015

24/10/2015

25/10/2015

26/10/2015

27/10/2015

28/10/2015

29/10/2015

30/10/2015

31/10/2015

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

229

248

244

154

206

241

128

232

248

238

241

220

248

114

235

247

244

248

247

239

89

246

245

230

223

245

244

109

248

6404 01/10/2015 02/10/2015 03/10/2015 04/10/2015 05/10/2015 06/10/2015 07/10/2015 08/10/2015 09/10/2015 10/10/2015 11/10/2015 12/10/2015 13/10/2015 14/10/2015 15/10/2015 16/10/2015 17/10/2015 18/10/2015 19/10/2015 20/10/2015 21/10/2015 22/10/2015 23/10/2015 24/10/2015 25/10/2015 26/10/2015 27/10/2015 28/10/2015 29/10/2015 30/10/2015 31/10/2015 Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Total Fcst 229 248 244 154 206 241 128 232 248 238 241 220 248 114 235 247 244 248 247 239 89 246 245 230 223 245 244 109 248 6404 <colgroup><col><col><col><col><col><col span="31"></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col span="31"></colgroup><tbody>
</tbody>

Many thanks,

Richard

 

[Sinon]

Have you tried a SUMIFS? Put the start date in one cell, let's say D1, the end date in Cell D2:
=SUMIFS(C1:C32,A1:A32,">="&F2,A1:A32,"<="&F3)

Alternatively, you can use a pivot table and group the dates by Days, select 7 days and select starting date as the first Friday of the month, 02/10/15 in your case. Have a look at the workbook I created here:
https://www.dropbox.com/s/p4ayf422ur4scpx/Date example.xlsx?dl=0