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:

[TABLE="width: 225"]
<tbody>[TR]
[TD]Arrive[/TD]
[TD]Depart[/TD]
[TD]Total_Days[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2016[/TD]
[TD="align: right"]4/24/2016[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD="align: right"]4/4/2016[/TD]
[TD="align: right"]4/27/2016[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="align: right"]4/6/2016[/TD]
[TD="align: right"]4/30/2016[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD="align: right"]4/8/2016[/TD]
[TD="align: right"]5/3/2016[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2016[/TD]
[TD="align: right"]5/5/2016[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD="align: right"]3/27/2016[/TD]
[TD="align: right"]4/1/2016[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]3/29/2016[/TD]
[TD="align: right"]4/3/2016[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2016[/TD]
[TD="align: right"]4/6/2016[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2016[/TD]
[TD="align: right"]4/10/2016[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[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