Date help

HI*

I have attached a workbook with 2 sheets, the formulas are in sheet 2 that I am using (E4), currently I have it doing a sumifs based on a specific number,specfic word (in Cell A1) and if a date falls between two dates in cell E2&E3)

In one sense it is working but you can see it totals April to 32. I want it to only count until the end of the month and then carry it over to the next month.

I have put an example of what I mean in yellow on sheet 2*

Any help would be appreciated*

Hi Shukla

That seems to be someway towards working, in reality there will be multiple rows on sheet one for the same number, when I add more rows it doesn't seem to add them up?

Thanks

I have attached the document with more data.

The formula you had worked on the one row, sheet 1 now has a lot more date and as well as splitting it out into the relevant months I need it to add all instances also.

Thanks so much for your help

To look up each cost centre 1,2 & 3 on sheet 1 and it gives a total of days in April ,may , June etc..

And as with you first formula it splits the total days into month

E.g. it might say 40 days from 20/06/2017 Which needs to show as 10 days in June and 30 days in July

Does that make sense?

Hi I have put the formula in to sheet2 E4 - E6 then across, I am getting numbers but not ones that math the amount

In yellow on sheet 2 for store 1 I have put a expected outcome

April total = 4 days
May Total = 7 Days
June Total = 65Days this is where you can see on sheet 1 in yellow there are 2 carry over dates as the amount of days exceeds the amount in the month
July Total = 12 Days

Hope that helps?

It looks like instead of treating each row as individual on sheet one it is taking the lowest from date and the highest to date within the month and returning the days based on that

So I seem to have two formulas that sort of do what I want but I just need to combine them,

Formula 1
This formula returns a value based on all the correct criteria but does not split the overlapping dates into the correct months

=SUMIFS('Sheet 1'!$G$3:$G$58,'Sheet 1'!$B$3:$B$58,$C5,'Sheet 1'!$F$3:$F$58,$A$1,'Sheet 1'!$H$3:$H$58,">="&Q$2,'Sheet 1'!$H$3:$H$58,"<="&Q$3)

Formula 2
This formula returns the correct days per month but is only working on 1 row rather than combining all the rows that fall under the sumifs criteria

=MAX(0,MIN(EOMONTH(Q2,0),$I3)-MAX(Q2,$H3)+1)