0

I have a situation where I am receiving payments. There are 2 types of payments 1. Payment in full and 2. Payment in 1-2-3-4 installements.

Here you can find an Example sheet of data. I only have the first payments of the installments so for example if you have 4 installments and the amount next to it is 125,00 EUR. It will mean, the customer has to pay 125,00 EUR this month, next month and the 2 months after that.

I am wondering, how do I calculate the amount of money I will receive each month?

What I have tried so far:

I have tried using =IF formula's and using QUERY to filter by date and then only taking those amounts, but for some reason it keeps on taking the wrong amount.

Any help would be greatly appreciated!

Scraper2022
  • 41
  • 6

2 Answers2

2

Added formula to to your sheet. Please do test it out:

=index(let(a,query(REDUCE({"",""},sequence(counta(A2:A)),lambda(a,c,{a;eomonth(edate(index(A2:A,c),sequence(index(B2:B,c),1,0,1)),),if(sequence(index(B2:B,c)),index(C2:C,c))})),"Select Col1, sum(Col2) Where Col1 is not null group by Col1 label sum(Col2) ''"),transpose({text(index(a,,1),"MMMM"),index(a,,2)})))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
0

I wonder if you could use a simple sumifs like this:

=ArrayFormula(sumifs($C2:$C,$A2:$A,"<="&eomonth(F5&"/2023",0),eomonth($A2:$A,$B2:$B-1),">="&F5&"/2023"))

Or as a lambda:

=ArrayFormula(map(F5:I5,lambda(x,sumifs(C2:C,A2:A,"<="&eomonth(x&"/2023",0),eomonth(A2:A,B2:B-1),">="&x&"/2023"))))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37