## [Solved] Checking payments

### [Solved] Checking payments

In the attached file, I'd like to have an additional column that shows the difference between 10% of the amount in G and the total payments (negative amounts) made for the previous month, with the month changing on the 26th of each month.
Having a hard time wrapping my head around ensuring that the amounts summed are both negative and between the 26th of the previous month and the date in that row of column A.
The time period would actually be from the 27th of the previous month to the date in column A.
I hope this makes sense.
Attachments
Sample.ods
Last edited by RusselB on Wed May 29, 2019 2:17 am, edited 1 time in total.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6057
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Checking payments

RusselB wrote:amounts summed are both negative and between the 26th of the previous month and the date in that row of column A
In H2, use =DATE(YEAR(A2);MONTH(A2)-1;27) to get the 27th of the previous month. Fill down. I will guess that column A is in ascending order and the formula only needs to examine previous rows. Leave I2 empty since there are no previous rows. In I3 calculate the sum with =SUMPRODUCT(C\$2:C2;C\$2:C2<0;A\$2:A2>=H3). Fill down.

You may prefer to move the auxillary calculation in H to ZZ or some other unused column which you will not see. I wasn't sure from your description if you will also want condition A\$2:A2<A3 as a fourth parameter.

SUMPRODUCT tutorial

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3979
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Checking payments

Thanks... that's a lot of help, especially that Tutorial (not sure how I missed that one when I was searching before posting).
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6057
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON