How do I add a range by date over 90 days older than today
 

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

How do I add a range by date over 90 days older than today
 

Try this.

=SUMPRODUCT(--(DATEDIF(B2:B5,TODAY(),"d")+1=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: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513244

How do I add a range by date over 90 days older than today
 

I'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

"SteveG" wrote:


Try this.

=SUMPRODUCT(--(DATEDIF(B2:B5,TODAY(),"d")+1=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: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513244

How do I add a range by date over 90 days older than today
 

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(),"d")+130),($C$2:$C$6))

60 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+160),($C$2:$C$6))

90 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+190),($C$2:$C$6))

120 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+1120),($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.

="Total over 30 days"&"
$"&SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+130),($C$2:$C$6))


Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513244

How do I add a range by date over 90 days older than today
 

Thank you so much it worked

"SteveG" wrote:


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(),"d")+130),($C$2:$C$6))

60 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+160),($C$2:$C$6))

90 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+190),($C$2:$C$6))

120 days
=SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+1120),($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.

="Total over 30 days"&"
$"&SUMPRODUCT(--(DATEDIF($B$2:$B$6,TODAY(),"d")+130),($C$2:$C$6))


Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513244