Page 1 of 1

[Solved] Checking payments

PostPosted: Tue May 28, 2019 11:10 pm
by RusselB
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.

Re: Checking payments

PostPosted: Wed May 29, 2019 12:06 am
by MrProgrammer
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.

Re: Checking payments

PostPosted: Wed May 29, 2019 2:17 am
by RusselB
Thanks... that's a lot of help, especially that Tutorial (not sure how I missed that one when I was searching before posting).