Using Today function in a formula

[Ed Denmark]

I want to sum a list of invoices that have not been paid.
I use the date the check is to be deposited for my paid
column. However some times I receive a check that is post
dated, which i want to keep seperate until the check can
be deposited. I want to subtotal those amounts in a
different sum. I believe I need to use the Today
function to help make this happen, but I do not know how
to properly enter this function into an IF statement
example:

line invoice ck date
1. $3250.10 09/22/03
2. $4832.23 09/14/03
3. $8765.32
4. $1923.11 10/08/03
5. $1822.33
6. $1200.00 09/13/03

So my totals should be. Paid $9282.33 Post Dated
1923.11 and Open $10587.65

Thank you in advance Ed

 

[Dan E]

Ed,

Two sets of options, assume your invoice amounts are
in B2:B7 and your dates in C2:C7

1. Combinations of Sum's and Sumif's
Paid:
=SUM(B2:B7)-SUMIF(C2:C7,">" & TODAY(),B2:B7)-SUMIF(C2:C7,"",B2:B7)
Post Dated:
=SUMIF(C2:C7,">" & TODAY(),B2:B7)
Open:
=SUMIF(C2:C7,"",B2:B7)
As you can see Paid is a sum off all minus the PD and O

2. Sumproducts
Paid:
=SUMPRODUCT((C2:C7<>"")*(C2:C7<=TODAY())*B2:B7)
Post Dated:
=SUMPRODUCT((C2:C7>TODAY())*B2:B7)
Open:
=SUMPRODUCT((C2:C7="")*(B2:B7))
As you can see Paid is a sum off all minus the PD and O

Dan E

 

[Ed Denmark]

-----Original Message-----
Ed,

Two sets of options, assume your invoice amounts are
in B2:B7 and your dates in C2:C7

1. Combinations of Sum's and Sumif's
Paid:
=SUM(B2:B7)-SUMIF(C2:C7,">" & TODAY(),B2:B7)-SUMIF (C2:C7,"",B2:B7)
Post Dated:
=SUMIF(C2:C7,">" & TODAY(),B2:B7)
Open:
=SUMIF(C2:C7,"",B2:B7)
As you can see Paid is a sum off all minus the PD and O

2. Sumproducts
Paid:
=SUMPRODUCT((C2:C7<>"")*(C2:C7<=TODAY())*B2:B7)
Post Dated:
=SUMPRODUCT((C2:C7>TODAY())*B2:B7)
Open:
=SUMPRODUCT((C2:C7="")*(B2:B7))
As you can see Paid is a sum off all minus the PD and O

Dan E




.Dan, Thank you for the formula, However if I want to

continue to ad new transactions to my list, how do I
expand the range to include the new invoices?

 

[Dan E]

Ed,

If you have nothing else in the range (other numbers to
mess up the sums You could use B:B and C:C (all of
columns b and c) ie

=SUM(B:B)-SUMIF(C:C,">" & TODAY(),B:B)-SUMIF(C:C,"",B:B)
=SUMIF(C:C,">" & TODAY(),B:B)
=SUMIF(C:C,"",B:B)

this won't work with the sumproduct formulas...

OR you could use a larger Range B2:B1000 and C2:C1000
note (the ranges must be the same sizes) which will work
with the sumproduct formulas ie

=SUMPRODUCT((C2:C1000<>"")*(C2:C1000<=TODAY())*B2:B1000)
=SUMPRODUCT((C2:C1000>TODAY())*B2:B1000)
=SUMPRODUCT((C2:C1000="")*(B2:B1000))

and the sum/sumif formulas

=SUM(B2:B1000)-SUMIF(C2:C1000,">" & TODAY(),B2:B1000)-SUMIF(C2:C1000,"",B2:B1000)
=SUMIF(C2:C1000,">" & TODAY(),B2:B1000)
=SUMIF(C2:C1000,"",B2:B1000)

Again, all of the ranges should be the same size!!!

Dan E