SUMIFS based on date
 

I am using the following sumifs statement:
=(SUMIFS(EVERYTHING!$D$1:$D$3856,EVERYTHING!$B$1:$ B$3856,A2,(EVERYTHING!$E$1:$E$3856),"<07/13/2008"))/1000000

to try to show how much data is being stored by user that is older than 3
months in Megabytes. This formula works pretty good but now I want to show
what is older than 6 months, 9 months, etc... and I want to use the TODAY()
function instead of using a specific date (7/13/2008) but I cannot seem to
get it to work. The EVERYTHING worksheets contains usernames in column B,
file sizes in column D and the date the data was modified in column E in
mm/dd/yyyy format. When I substitute 07/13/2008 for TODAY()-90 I just get
0.000 where as the 7/13/2008 results in a real number (4918.228). What am I
missing here.

SUMIFS based on date
 

Try this


replace this part


"<07/13/2008"


with


"<"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))



I haven't tested because I don't have 2007 on this PC but
it works for a regular SUMIF in 2003

--


Regards,


Peo Sjoblom

"SeanU" wrote in message
...
I am using the following sumifs statement:
=(SUMIFS(EVERYTHING!$D$1:$D$3856,EVERYTHING!$B$1:$ B$3856,A2,(EVERYTHING!$E$1:$E$3856),"<07/13/2008"))/1000000

to try to show how much data is being stored by user that is older than 3
months in Megabytes. This formula works pretty good but now I want to
show
what is older than 6 months, 9 months, etc... and I want to use the
TODAY()
function instead of using a specific date (7/13/2008) but I cannot seem to
get it to work. The EVERYTHING worksheets contains usernames in column B,
file sizes in column D and the date the data was modified in column E in
mm/dd/yyyy format. When I substitute 07/13/2008 for TODAY()-90 I just get
0.000 where as the 7/13/2008 results in a real number (4918.228). What am
I
missing here.

SUMIFS based on date
 

Don't have SUMIFS on my ver but believe it needs to be expressed as:
"<"&TODAY()-90, not: "<TODAY()-90"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:61
xdemechanik
---

SUMIFS based on date
 

Peo,

I got to work with a simpler formula - I replaced the "<07/13/2008" with
"<"&TODAY()-90 and it works. Thanks for showing the '&' in your formula,
that was the ticket.

Sean

"Peo Sjoblom" wrote:

Try this


replace this part


"<07/13/2008"


with


"<"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))



I haven't tested because I don't have 2007 on this PC but
it works for a regular SUMIF in 2003

--


Regards,


Peo Sjoblom

"SeanU" wrote in message
...
I am using the following sumifs statement:
=(SUMIFS(EVERYTHING!$D$1:$D$3856,EVERYTHING!$B$1:$ B$3856,A2,(EVERYTHING!$E$1:$E$3856),"<07/13/2008"))/1000000

to try to show how much data is being stored by user that is older than 3
months in Megabytes. This formula works pretty good but now I want to
show
what is older than 6 months, 9 months, etc... and I want to use the
TODAY()
function instead of using a specific date (7/13/2008) but I cannot seem to
get it to work. The EVERYTHING worksheets contains usernames in column B,
file sizes in column D and the date the data was modified in column E in
mm/dd/yyyy format. When I substitute 07/13/2008 for TODAY()-90 I just get
0.000 where as the 7/13/2008 results in a real number (4918.228). What am
I
missing here.