Array Sum Room Nights

[ricafonyat]

Hello!!

I am looking for help to build an array formula that compute by month and year the number of room nights according the hotel reservations book

Hotel Reservations
Check-in Date	Check-out Date	Room Nights
Apr.30.2017	May.02.2017	2
May.01.2017	May.02.2017	1
May.03.2017	May.06.2017	3
May.10.2017	May.11.2017	1
May.12.2017	May.15.2017	3
May.30.2017	Jun.02.2017	3
Jun.03.2017	Jun.05.2017	2
Jun.30.2017	Jul.01.2017	1
Jul.02.2017	Jul.05.2017	3
Jul.06.2017	Jul.10.2017	4
Jul.15.2017	Jul.22.2017	7
Jul.25.2017	Aug.01.2017	7
Total Room Nights	37

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Month	Room Nights
Jan.17	?
Feb.17	?
Mar.17	?
Apr.17	?
May.17	?
Jun.17	?
Jul.17	?
Aug.17	?
Sep.17	?
Oct.17	?
Nov.17	?
Dec.17	?
Total Room Nights	37

<colgroup><col><col></colgroup><tbody>
</tbody>

Any help would be appreciated
Rica

 

[Marcelo Branco]

Try

Dates, numbers not text, in columns A:B as dd/mm/yyyy
Months, numbers not text, in E2:E13
Some abbreviations of the months in Portuguese are different from English but very similar (fev instead of feb, abr instead of apr, etc...)

	A​	B​	C​	D​	E​	F​
1​	Check-in Date​	Check-out Date​	Room Nights​		Month​	Room Nights​
2​	30/04/2017​	02/05/2017​	2​		jan/17​	0​
3​	01/05/2017​	02/05/2017​	1​		fev/17​	0​
4​	03/05/2017​	06/05/2017​	3​		mar/17​	0​
5​	10/05/2017​	11/05/2017​	1​		abr/17​	1​
6​	12/05/2017​	15/05/2017​	3​		mai/17​	11​
7​	30/05/2017​	02/06/2017​	3​		jun/17​	4​
8​	03/06/2017​	05/06/2017​	2​		jul/17​	21​
9​	30/06/2017​	01/07/2017​	1​		ago/17​	0​
10​	02/07/2017​	05/07/2017​	3​		set/17​	0​
11​	06/07/2017​	10/07/2017​	4​		out/17​	0​
12​	15/07/2017​	22/07/2017​	7​		nov/17​	0​
13​	25/07/2017​	01/08/2017​	7​		dez/17​	0​
14​	Total Room Nights​		37​		Total Room Nights​	37​

Array formula in F2 copied down (gray area)
=SUM(IF(1-((A$2:A$13>EOMONTH(E2,0))+(E2>B$2:B$13)),IF(B$2:B$13>EOMONTH(E2,0),1+EOMONTH(E2,0),B$2:B$13)-IF(A$2:A$13>E2,A$2:A$13,E2)))
Ctrl+Shift+Enter

M.

 

[ricafonyat]

Thanks Marcelo!
It works perfectly!
Are you from Brasil?
Best Regards

 

[Marcelo Branco]

Yes, i live in Rio.

You are welcome. Thanks for the feedback.

M.