Formula to count days in array based on criteria within a specific month

[Dman333]

Does anyone know if there is a method or function in excel that will total all the days in April and May separately? I have a large data set and calculate occupancy within a specific date range based on multiple criteria (using sumifs). Now I want to calculate the occupancy by month within that date range which could be 1 month or 1 year. A small example of a data set is below:

Arrive	Depart	Total_Days
4/2/2016	4/24/2016	21
4/4/2016	4/27/2016	22
4/6/2016	4/30/2016	23
4/8/2016	5/3/2016	24
4/10/2016	5/5/2016	24
3/27/2016	4/1/2016	4
3/29/2016	4/3/2016	4
3/31/2016	4/6/2016	5
4/2/2016	4/10/2016	7

<tbody>
</tbody>

 

[James006]

Hi,

Take a look at the following solution proposed by Biff ...

http://www.mrexcel.com/forum/excel-questions/652190-counting-days-specific-month-between-two-dates-2.html#post3238741

HTH

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H	I
1
2	Arrive	Depart		Total_Days	01-03-16	01-04-16	01-05-16	01-06-16
3	2-Apr-16	24-Apr-16		23	0	23	0
4	4-Apr-16	27-Apr-16		24	0	24	0
5	6-Apr-16	30-Apr-16		25	0	25	0
6	8-Apr-16	3-May-16		26	0	23	3
7	10-Apr-16	5-May-16		26	0	21	5
8	27-Mar-16	1-Apr-16		6	5	1	0
9	29-Mar-16	3-Apr-16		6	3	3	0
10	31-Mar-16	6-Apr-16		7	1	6	0
11	2-Apr-16	10-Apr-16		9	0	9	0
12				152	9	135	8
1d

Cell Formulas
Range	Formula
E3	=C3-B3+1
F3	=MAX(0,MIN(G$2,$C3+1)-MAX(F$2,$B3))
G3	=MAX(0,MIN(H$2,$C3+1)-MAX(G$2,$B3))
H3	=MAX(0,MIN(I$2,$C3+1)-MAX(H$2,$B3))

N.B.
copy the formula down
sum the columns
This suggestion includes both the Start Date and the End Date

 

[Dman333]

Thanks - That works well though I was hoping to have a single cell for the formula and not have to add a bunch of columns to the worksheet because I'm just looking for the summary data. I don't think excel has a single function that will do this.
Thanks for the help!

 

[Dman333]

Thanks