Google Spreadsheet Sum based on multiple date comparisons using MMULT

Question:

( by Jeff Richards )

If a date, held in the D column of a sheet falls within a given week, Sum the E column. 

I am currently using:

=SUMIF('Accounts Receivable'!D2:D,'Accounts Receivable'!D2:D>A3 & 'Accounts Receivable'!D2:D<B3 ,'Accounts Receivable'!E2:E), which is almost, but not quite, working.

Where Accounts Receivable Column D is the due date of invoices, Column E is the amount of the invoice, and A3 and B3 of the given sheet define a week (8/12/13 and 8/19/13).

The object is to get a week by week look at what income will be coming in. So, for the week of 8/12-8/18, Sum all the invoices that are expected to be paid that week. Next row down, look at the week of 8/19-8/27, etc.

Thanks for any help!

================

There are 3 sheets on the spreadsheet.  On the Main Sheet, the Week by Week Summary, I would like to display a running total of our balance (column C), along with that weeks expected income (column D) and that weeks expected payments (column E).

On sheet 2, Accounts Receivable, we are simply adding invoices as they are sent out, with a little simple math to calculate the date they are due.  For projecting (and because our clients are reliable), we're simply assuming payment is received on the day it is due.

On sheet 3, Accounts Payable, we add the invoices & bills we need to pay as they come in, which usually requires manual entry of the due date.  For projecting, we're assuming the payment will arrive on the due date.

Back to the Main Sheet.  I would like, on each line of the week by week summary, to have it sum up any invoices on Accounts Receivable that are due that week, and put that number in column D of the week by week summary.  On Column E, I'd like to have the same thing happen with Accounts Payable.

This is my current attempt at the formula for Accounts Receivable.

=SUMIF('Accounts Receivable'!D2:D,'Accounts Receivable'!D2:D>A3 & 'Accounts Receivable'!D2:D<B3 ,'Accounts Receivable'!E2:E)

I am trying to say: Check this in all cells in Accounts Receivable Column D. If the date in Column D is between A3 (beginning of week 1) and B3 (end of week 1), add the value in Accounts Receivable Column E.

I would expect Week 1 to have 15,000 in the Accounts Receivable, but I'm getting 0.

This:

=SUM(IF(A3<'Accounts Receivable'!D3:D & B3>'Accounts Receivable'!D3:D,'Accounts Receivable'!E3:E), -1)

Gets me 31,499.  Which isn't a number that should ever occur (I used nice round 100s in the dummy data), and it isn't right for ALL the accounts receivable, but it seems like a strange subset).

Thanks for any help!

Solution:

Have a look at the following screenshot of "Accounts Receivable":

Have a look at the following screenshot of "Accounts Payable":

Have a look at the following screenshot of Sheet "Week by Week Summary":

In the above sheet, I have the following formulas in Cell 

D2:

=arrayformula(if(isblank(A2:A);iferror(1/0);MMULT((sign(A2:A<=TRANSPOSE('Accounts Receivable'!D2:D))*(B2:B>=TRANSPOSE('Accounts Receivable'!D2:D)));'Accounts Receivable'!E2:E)))

E2:

=arrayformula(if(isblank(A2:A);iferror(1/0);MMULT((sign(A2:A<=TRANSPOSE('Accounts Payable'!C2:C))*(B2:B>=TRANSPOSE('Accounts Payable'!C2:C)));'Accounts Payable'!D2:D)))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive