Sumifs for a forecast

[xxkaykayxx]

Hi, So I am trying to create a weekly cash-flow forecast, that pulls data from another tab and using the expected payment date phases the income into the correct weeks by client. I have seen this done before but my formula is slightly off it seems.

A	B	C	D	E	F	G
Cash flow Forecast
Week Ending		01/08/17	07/08/17	14/08/17	21/08/17	28/08/17
27/08/17	31/07/17	06/08/17	13/08/17	20/08/17	27/08/17	03/09/17
Client A
Client B

<tbody>
</tbody>

The formula i was attempting was: =SUMIFS('Debtors List'!$F:$F,'Debtors List'!$A:$A,"'Debtors List'!A5",'Debtors List'!$E:$E,">"&B$4,'Debtors List'!$E:$E,"<"&D$3)

Debtors List Tab: (Col A-F)

Client Code	Client ShortName	Invoice No	Date	Expected Payment Date	Balance
AAAA		0001	25/07/17	24/08/17	10000
AAAA		0002	31/07/17	30/08/17	12000
BBBB		0003	1/08/17	31/08/17	5000

<tbody>
</tbody>

Please help if you can

 

[duckandcover]

Try this

=SUMIFS('Debtors List'!$F$2:$F$4,'Debtors List'!$A$2:$A$4,$A4,'Debtors List'!$E$2:$E$4,">"&C2,'Debtors List'!$E$2:$E$4,"<"&C3)

I've made some assumptions from your post, these are

Your week start date is in a cell and the week end date is in the cell below it

Also I have put the client name in column A on the second sheet and used that as a reference to search

I've got a copy of what you've posted and it appears to work for me

 

[xxkaykayxx]

Try this

=SUMIFS('Debtors List'!$F$2:$F$4,'Debtors List'!$A$2:$A$4,$A4,'Debtors List'!$E$2:$E$4,">"&C2,'Debtors List'!$E$2:$E$4,"<"&C3)

I've made some assumptions from your post, these are

Your week start date is in a cell and the week end date is in the cell below it

Also I have put the client name in column A on the second sheet and used that as a reference to search

I've got a copy of what you've posted and it appears to work for me

Brilliant. I will give it a try and let you know

 

[xxkaykayxx]

It works brilliantly, Thanks so much for your help