Sumproduct/Sumifs help please

[denise6372]

I'm slowly driving myself mad with this one and can't seem to figure it out.
I'm compiling a spreadsheet for my business and have set formulas to enable me to calculate easily how much I've invoiced between two certain dates.

Now I want to calculate how much value is in invoices unpaid between two certain dates.

I have columns as follows:
1)Invoice date
2) Invoice Amount
3) Paid date
and on a separate tab have cells to enter start date and end date to so I can chose how long a period I'm trying to work out. Ideally it will calculate by adding the lines with no date in the "paid date" column.

Hope that makes sense to somebody and would really appreciate some help.
Many thanks/

 

[Aladin Akyurek]

I'm slowly driving myself mad with this one and can't seem to figure it out.
I'm compiling a spreadsheet for my business and have set formulas to enable me to calculate easily how much I've invoiced between two certain dates.

Now I want to calculate how much value is in invoices unpaid between two certain dates.

I have columns as follows:
1)Invoice date
2) Invoice Amount
3) Paid date
and on a separate tab have cells to enter start date and end date to so I can chose how long a period I'm trying to work out. Ideally it will calculate by adding the lines with no date in the "paid date" column.

Hope that makes sense to somebody and would really appreciate some help.
Many thanks/

Try...

=SUMPRODUCT(
     --(InvoiceDate>=StartDate),
     --(InvoiceDate<=EndDate),
     --ISNUMBER(PaidDate),
     InvoiceAmount)

which will yield total paid amount between a start date and an end date.

=SUMPRODUCT(
     --(InvoiceDate>=StartDate),
     --(InvoiceDate<=EndDate),
     1-ISNUMBER(PaidDate),
     InvoiceAmount)

which will yield total yet unpaid amount between a start date and an end date.

If you are on Excel 2007 or later, try...

=SUMIFS(
     InvoiceAmount,
     InvoiceDate,">="&StartDate,
     InvoiceDate,"<="&EndDate,
     PaidDate,"<>")

=SUMIFS(
     InvoiceAmount,
     InvoiceDate,">="&StartDate,
     InvoiceDate,"<="&EndDate,
     PaidDate,"=")