B
B
billid2019-03-31 20:48:07
Spreadsheets
billid, 2019-03-31 20:48:07

How can I force the result to be automatically added to the newly added rows by the arrayformula and offset functions?

Help please, it is extremely important to find a solution!
5ca0fdd0b5782751215940.jpeg
Purpose:
There are two cells (in the picture it is "Actual payment date 1" and "Payment amount 1"), the formulas of which I can't get correctly automatically copied into the cells below when adding new rows using arrayformula and offset, I tried a bunch of options, but it didn’t work out, everything that happened, the result of the first one was automatically copied into the following cells and that’s it.
The most interesting thing is that I found two different ways of writing these formulas, the result of which is correct in both cases, but in both cases the formulas refuse to be automatically copied to new lines.
What do these cells do (what do I want from them in general):
1. (already done) Set the last date of payment according to the contract number in the interval between the dates (the maximum date in the interval between the specified dates according to a certain contract number), i.e. you need to make a selection.
for example: find all payments under contract 325 (suppose there are 5 of them) between 03/20/2019 and 04/20/2019 and display the payment with the largest date as a result, or rather its date.
2. (already done) You need to sum up all payments for a specific contract number between the specified payment dates.
for example: find all payments under contract 325 (suppose there are 5 of them) between 03/20/2019 and 04/20/2019 and summarize them.
The file itself
There are 4 sheets in the file:
1. Ex1 - the first version of the formulas (without arrayformula and offset)
2. Ex2 - the second version of the formulas (without arrayformula and offset)
3. for-ex1 - my attempt to force autocomplete (with arrayformula and offset)
4. Bills - payments where the sample is taken from

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question