Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using sumifs to sum based on month, and criteria | Excel Worksheet Functions | |||
business day date from a specific date based on a number of days | Excel Worksheet Functions | |||
input a date or update it based on date in another cell | New Users to Excel | |||
Sumproduct based which also weights data based on date | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |