• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fomula to populate monthly data from changing criteria

J

jonnieRC

New Member
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
 

Attachments

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))
 
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
 

Attachments

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
 
You must log in or register to reply here.
Back
Top