Help in macro indicating number of people going on a holiday on the same week

[grlnexdor]

Hi Everyone,


I would appreciate your help on this. I'm currently learning how to create simple macro but couldn't get my head this one.


Basically I just want to identify the number of people travelling on the same day in a week.

I want my result to be shown as below:

Dates 1-5 Dec 1985, 8 (no of people)
6-10 Dec 1985, 1 (no of people)
11-15 Dec 1985, 2 (no of people)
16-20 Dec 1985, 4 (no of people)
21-25 Dec 1985, 1 (no of people)
26-31 Dec 1985 6 (no of people)


NAME DATES
A 12/4/1985
B 12/8/1985
C 12/13/1985
D 12/14/1985 E 12/21/1985 F 12/26/1985
G 12/3/1985
H 12/28/1985
I 12/19/1985
J 12/3/1985
K 12/3/1985
L 12/5/1985
M 12/3/1985
N 12/17/1985
O 12/17/1985
P 12/30/1985
Q 12/30/1985
R 12/5/1985
S 12/17/1985
T 12/30/1985
U 12/5/1985
V 12/30/1985


Many Thanks in advance.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[grlnexdor]

Hi Sixth Sense,

I just did upload an attachment.. let me know if the information required is enough.

Thanks!

[xlbiznes]

Hi,

this will do the job :

=SUMPRODUCT(--($B$2:$B$21>=D1),--($B$2:$B$21<=E1))

Enter the start date and end data in cells d1 and e1 respectively and your data table is starting from cell a1.

[millz]

Try the attached. If you want to add a longer list of the date range, just add on from E9 onwards, and in the same format as above.

[grlnexdor]

works great! thank you so much!!