excel - Sum the number of days in one date range which fall within a second date range -

i have 2 columns of dates. 1 move in date , other move out date. want figure out how many days tenant there during second date range. example: how many total "bed days" did have in month of july? 7/1/2016-7/31/2016

this function calculates number of days each tenant there each month if entire calculation 1 cell without creating dummy column each month.

=max(0,min(eomonth($b$2,0),i14)-max($b$2,h14)) 

i tried change few things , use array function not working. new array functions may doing wrong.

=sum(max(0,min(eomonth($b$2,0),i:i)-max($b$2,h:h))) 

any appreciated! let me know if need more info too.

bad news - can't use max , min in array formula because instead of treating h , 2 arrays treats them 1 big long array , 1 value out of them.

you need add 1 original formula because if moved in on last day of month (say) should still count 1 day.

if replace max , min if statements this

=sum(if(if(eomonth($b$2,0)<if(i2:i10="active",today(),i2:i10),eomonth($b$2,0),if(i2:i10="active",today(),i2:i10))-if($b$2>h2:h10,$b$2,h2:h10)<0,0, if(eomonth($b$2,0)<if(i2:i10="active",today(),i2:i10),eomonth($b$2,0),if(i2:i10="active",today(),i2:i10))-if($b$2>h2:h10,$b$2,h2:h10)+1)) 

which has entered using ctrlshiftenter

a useful tip if new arrays not include more rows in array formula need because slow, , test first on small number of rows can step through using evaluate formula if run trouble.