Page 1 of 1

### [Solved] Checking payments

Posted: Tue May 28, 2019 11:10 pm
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

Posted: Wed May 29, 2019 12:06 am
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

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