Help please, it is imperative to find a solution!

Goal:

There are two cells (in the picture it is "a Fact. date of payment 1 and payment 1"), the formulas of which I can never get correctly automatically copied to the cell below when adding new rows using arrayformula and offset, tried a bunch of options, but it did not work, all it did was the result of the first machine to be copied to the next cell and everything.

Most interesting, I found two different ways of writing these formulas, the result of which in both cases is correct, but in both cases, formula refuse automatically copied to the new row.

What do these cells (what do I want from them):

1. (this is done) to Put the last payment date on the contract number in the interval between the dates (a maximum date between specified dates for a specific contract number), ie you need to make a selection.

example: to find all payments under the contract 325 (assuming 5 PCs) between 20.03.2019 and 20.04.2019 the payment, the date of which the largest, and output as the result, more precisely its date.

2. (this is already done) you Need to add up all payments for a particular contract number between the specified dates of payment.

example: to find all payments under the contract 325 (assuming 5 PCs) between 20.03.2019 and 20.04.2019 and summarize them.

The file itself

In the file sheet 4:

1. Ex1 is the first variant of formulas (without arrayformula and offset)

2. Ex2 is the second version of the formulas (without arrayformula and offset)

3. for-ex1 - my attempt to make autosemantic (with arrayformula and offset)

4. Bills - payments where does the sample

Goal:

There are two cells (in the picture it is "a Fact. date of payment 1 and payment 1"), the formulas of which I can never get correctly automatically copied to the cell below when adding new rows using arrayformula and offset, tried a bunch of options, but it did not work, all it did was the result of the first machine to be copied to the next cell and everything.

Most interesting, I found two different ways of writing these formulas, the result of which in both cases is correct, but in both cases, formula refuse automatically copied to the new row.

What do these cells (what do I want from them):

1. (this is done) to Put the last payment date on the contract number in the interval between the dates (a maximum date between specified dates for a specific contract number), ie you need to make a selection.

example: to find all payments under the contract 325 (assuming 5 PCs) between 20.03.2019 and 20.04.2019 the payment, the date of which the largest, and output as the result, more precisely its date.

2. (this is already done) you Need to add up all payments for a particular contract number between the specified dates of payment.

example: to find all payments under the contract 325 (assuming 5 PCs) between 20.03.2019 and 20.04.2019 and summarize them.

The file itself

In the file sheet 4:

1. Ex1 is the first variant of formulas (without arrayformula and offset)

2. Ex2 is the second version of the formulas (without arrayformula and offset)

3. for-ex1 - my attempt to make autosemantic (with arrayformula and offset)

4. Bills - payments where does the sample

asked March 19th 20 at 09:26

0 answer

Find more questions by tags Google SheetsSpreadsheets