Variable in array formula

[PaulS]

To the experts,
I have a formula that works fine but from time to time I have to change the formula because of the number of rows (transactions) has changed.
The formula:
=SUM(IF(MONTH($B$12:$B$99)=ROW($A1);M$12:M$99;0))
sums the amounts in col M when the months in col B equals to 1. The same formula is in other cells for the other months.
The cells with the formula is 20 rows under the last transaction row. This means that the cell with ther formula also moves down when more trancations are added.
The number of transactions will grow over the year. In the worksheet cell AL10 has the row number of the last transaction.(in this formula 99).
I am looking for a solution to replace 99 in the formula into the contents of cell AL10.
Thanks,
Paul

 

[Aladin Akyurek]

What is the first cell you entered this formula?

 

[PaulS]

This formula is in my worksheet in cell M120
Because of the copy the brackets were removed, but the formula as shown is between { }.
Cell M121 contains the same formula but with "ROW($A2)" .
Hope this answers your question.
Paul

 

[Aladin Akyurek]

This formula is in my worksheet in cell M120
Because of the copy the brackets were removed, but the formula as shown is between { }.
Cell M121 contains the same formula but with "ROW($A2)" .
Hope this answers your question.
Paul

I assume that all data belongs to the same year...

Control+shift+enter, not just enter, and copy down:

=SUM(IF(MONTH($B$12:INDEX($B:$B,$AL$10))=ROWS($M$120:M120);
    $M$12:INDEX($M:$M,$AL$10)))

 

[PaulS]

It works.
Now I also learned how to use the INDEX function !
Thanks,
Paul