Allocate payment in respective month Project wise

[atuljadhav]

Hi expert,

i have two sheets 1) Invoice 2) Payment

1) Invoice

I have 3 Project (B), every project has 4 milestone (G) which have separate credit days (F) and due date (H),

2) Payment

Payment sheet has details for which project (B) for which milestone (C) the payment received (E) and when received (F).

now in Invoice Sheet i want to allocate this payment to particular month to particulate project and milestone.
for example we got payment of 10 against project_1 towards Supply, so this payment should add in March-21 Month in project_1 in Supply line.
you can refer attached excel, and if I change the date or add new payment then it should be allocate accordingly.
we will also add new invoice as well.

[Estevaoba]

Welcome to the forum.

Try in K4 and copy across and down:

Please Login or Register  to view this content.

Good luck!

[Gregb11]

If you are going to span years, the only thing I would add to the above is to check the year as well, like:

=IFERROR(SUMPRODUCT((Payment!$A$4:$A$100=Invoice!$A4)*(MONTH(Payment!$F$4:$F$100)=MONTH(Invoice!M$3))*(YEAR(Payment!$F$4:$F$100)=YEAR(Invoice!M$3))*(Payment!$E$4:$E$100)),"")

[bebo021999]

You may need to clear out all helper column (yellow collour) with this:

Please Login or Register  to view this content.

[atuljadhav]

i made small changes, link correction.
but now I got what I want, thanks to all.

=IFERROR(SUMPRODUCT((Payment!$A$4:$A$100=Invoice!$A4)*(MONTH(Payment!$F$4:$F$100)=MONTH(Invoice!K$3))*(YEAR(Payment!$F$4:$F$100)=YEAR(Invoice!K$3))*(Payment!$E$4:$E$100)),"")