Fomula to populate monthly data from changing criteria

[jonnieRC]

Hi Folks,

I am looking for a formula that will help me populate the attached monthly schedule from the Detail tab whereby if I change the Start date in the detail tab it will populate the Monthly break table and update for the ones that had different / staggered start dates (col F). I was tying to use a SUMIFS formula but am defo missing something. Im not sure if its a IF formula or what I am missing but i cant get it to work.

Any help would be really appreciated.

Kind regards,
John

 

[yaswanth92]

Paste the below formula in Cell F9 and copy it across till Cell N9 and check if this is the result you expected:

= SUMIFS(Detail!$T:$T, Detail!$D:$D, $B$4, Detail!$F:$F, ">=" & C$3, Detail!$F:$F, "<=" & EOMONTH(C$3, 0))

 

[jonnieRC]

Hi,

Thanks for coming back. It's not quite what I was looking for. For example its a monthly payment with new people coming on board at different months in the year. So i could expect each month to see the amount go up based on the on boarding date and to have an amount in each month.

Is this possible? I have provided a break in the attached of the kind of expectation of what i am tryingt o get to. I just need a formula as i ahve som many lines of data i need to do it for.

Really appreciate any help that can be offered.

Thanks,
John

 

[yaswanth92]

Hi,

It looks like you are calculating Year-To-Date payments, try the below formula and copy it across:

= SUMIFS(Detail!$P:$P, Detail!$D:$D, $C$4, Detail!$F:$F, ">=" & $D$3, Detail!$F:$F, "<=" & EOMONTH(D$3, 0)) / 12

 

[jonnieRC]

That's worked perfect, thanks so much.

Really appreciate it.