Hi I am currently helping a friend out with a reservation spreadsheet. The issue I have is certain dates fall into different price brackets.

For example B2 Start date (01/05/15) C2 End Date (14/05/15) however the dates cross two price bands 5 days in low season and the remaining in high season is there a formula that can count from a list how many dates fall in low and how many in high? I have a list on one worksheet that details every date and which is Low or High can someone help please

Cheers

2. Hello
Assuming dates in Col A, with a "Yes" flag in Cols B (if high) and C (if low). If we require totals for 200 dates we could place the following formulae in row 202:

1) In B202: =COUNTIF(B1:B200, "Yes")
2) In C202: =COUNTIF(C1:C200, "Yes")

Does that do it?

3. ## Thanks but think its a little more complicated

Originally Posted by Hercules1946
Hello
Assuming dates in Col A, with a "Yes" flag in Cols B (if high) and C (if low). If we require totals for 200 dates we could place the following formulae in row 202:

1) In B202: =COUNTIF(B1:B200, "Yes")
2) In C202: =COUNTIF(C1:C200, "Yes")

Does that do it?
I am working on a reservation spreadsheet, have sorted out the Gant sheet but need to calculate prices.

for example I have a booking 1st May - 7th May (3 days 1st-3rd are priced at £10 and 4th-7th are priced at £15, when I enter start date 1st May and end date 7th may I need a formula that calculates that over the date range 3 days are at 10 and 4 days are at 15

Ignore the....... needed to enter so it lined up!

Sheet 1

COL F........COL G........COL K.....COL L.......COL M
Start ........End ..........LOW........MID.........HIG
01/05/15...07/05/15......3............0.............4 (Need a formula to calculate Columns K / L / M) if at all possible??

I have list in Sheet 2 with details as follows
COL A.....COL B......COL C
01/05/15 03/05/15 LOW
04/05/15 04/06/15 MID
05/06/15 01/09/15 HIG
02/09/15 29/10/15 MID
30/10/15 07/11/15 HIG
08/11/15 29/12/15 LOW

4. Its possible that someone else might be able to suggest a suitable formula, but I think that this is made difficult by the way your data is organised. This applies in particular to the dates in your Sheet2 table, being organised in groups rather than listed individually. Working with what you have, I would suggest a VBA solution with possibly one or more user defined functions to pick out the correct dates.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•