Try:
=SUMIF(A2:A30, "<="&today(), B2:B30)
or if you have to specify a date:
=SUMIF(A2:A30, "<="&date(2007,10,19), B2:B30)

Maybe you could just include a similar formula in the "receiving" workbook:

=SUMPRODUCT(--([book1.xls]Sheet1!$A$2:$A$30<=&today(),
[book1.xls]Sheet1!$B$2:$B$30)

(=sumif() won't work if the sending workbook is closed.)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



wrote:

Hi,

Ive recently been given a new task at work to create a report of all overdue
invoices on excel 2007. Each record has basically a due date, and the amount
of the invoice, so i've used the formula (for example):

=SUMIF(A2:A30, "<=19/10/2007", B2:B30)

to sum the value of each invoice that is overdue (i.e has a due date less
than today). The first problem is that the "TODAY()" tool doesn't work in the
formula, nor does "NOW()", so i manually have to enter the days date in the
formula.

The second problem is how to extract the value this formula produces, and
how to put it into a new spreadsheet (i.e. the report im required to
produce), as there are many different spreadsheets this has to be done for.

Any help would be greatly appreciated and will save me a fair amount of time
each week!

--

Dave Peterson