software

I am setting up a work book that needs a cell to show an over 120 days old
total with out having a column above it. I have tried sumif but can't find a
criteria that will let me refferance a TODAY date or a cell with today's date
in it to subtract from.
my columns a Invoice #, Invoice Date, and Invoice Amount I would Like a
cell on the bottom that shows a total amount over 120 days old


Try this.

=SUMPRODUCT(--(DATEDIF(B2:B5,TODAY(),quot;dquot;) 1gt;=120))

B2:B5 are your dates. DATEDIF counts the days between the date in your
dates and TODAY. Add the 1 to include the start date.

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=513244I'm comfused??? how to apply this
here is an example of what I'm trying to do
TODAY 2/16/2006
INV 1 10/01/05 $100
INV 2 10/18/05 $100
INV 3 11/01/05 $100
INV 4 12/30/05 $100
INV 5 01/30/06 $100
total $500
total over 30 days $400
total over 60 days $300
total over 90 days $200
total over 120 days $100

I don't know how to apply the formular to the cells
Thanks so much

quot;SteveGquot; wrote:

gt;
gt; Try this.
gt;
gt; =SUMPRODUCT(--(DATEDIF(B2:B5,TODAY(),quot;dquot;) 1gt;=120))
gt;
gt; B2:B5 are your dates. DATEDIF counts the days between the date in your
gt; dates and TODAY. Add the 1 to include the start date.
gt;
gt; Does that help?
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=513244
gt;
gt;


Sorry, thought you wanted to count the number of instances that the
invoice was over 120 days. Try this. You need to alter this formula
for each # of days you have. 30,60,90,120. Using the dates you have
then.

30 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;30),($C$2:$C$6))

60 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;60),($C$2:$C$6))

90 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;90),($C$2:$C$6))

120 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;120),($C$2:$C$6) )

Your results should actually be
30 - $400
60 - $300
90 - $300
120 - $200

120 days from today would be 10/20/2005.

If you want to include the Text phrase in your example then use this
and modify as needed.

=quot;Total over 30 daysquot;amp;quot;
$quot;amp;SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;30),($C$2:$C$6))Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=513244Thank you so much it worked

quot;SteveGquot; wrote:

gt;
gt; Sorry, thought you wanted to count the number of instances that the
gt; invoice was over 120 days. Try this. You need to alter this formula
gt; for each # of days you have. 30,60,90,120. Using the dates you have
gt; then.
gt;
gt; 30 days
gt; =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;30),($C$2:$C$6))
gt;
gt; 60 days
gt; =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;60),($C$2:$C$6))
gt;
gt; 90 days
gt; =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;90),($C$2:$C$6))
gt;
gt; 120 days
gt; =SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;120),($C$2:$C$6) )
gt;
gt; Your results should actually be
gt; 30 - $400
gt; 60 - $300
gt; 90 - $300
gt; 120 - $200
gt;
gt; 120 days from today would be 10/20/2005.
gt;
gt; If you want to include the Text phrase in your example then use this
gt; and modify as needed.
gt;
gt; =quot;Total over 30 daysquot;amp;quot;
gt; $quot;amp;SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),quot;dquot;) 1gt;30),($C$2:$C$6))
gt;
gt;
gt; Does that help?
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=513244
gt;
gt;