Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct based on date entered in separate field
I'm a rusty Excel user, and designed a sheet to report on monthly values
"items closed" using Sumproduct as indicated below. Here are my fields on the report tab: Column B: Date checked (data entry cell) Column C: Number Submitted =SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$2000)=1),--(YEAR('Data Entry'!$B$2:$B$2000)=2009)) Column D: Number Completed =SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000=DATE(2009,1,1)),--('Data Entry'!$B$2:$B$2000<=DATE(2009,1,31))) I have now found that they want it reported "as of the date checked" which is sometime between the 25th and last date of the month, depending. I have a data entry field for the date checked, but I must have to change the way I'm pulling the date. It doesn't like just a plain old cell reference. Help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct based on date entered in separate field
Not sure what you're asking for.
If you enter an "as of the date checked" how should this be incorporated into your existing formulas? -- Biff Microsoft Excel MVP "Mercdoodle" wrote in message ... I'm a rusty Excel user, and designed a sheet to report on monthly values "items closed" using Sumproduct as indicated below. Here are my fields on the report tab: Column B: Date checked (data entry cell) Column C: Number Submitted =SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$2000)=1),--(YEAR('Data Entry'!$B$2:$B$2000)=2009)) Column D: Number Completed =SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000=DATE(2009,1,1)),--('Data Entry'!$B$2:$B$2000<=DATE(2009,1,31))) I have now found that they want it reported "as of the date checked" which is sometime between the 25th and last date of the month, depending. I have a data entry field for the date checked, but I must have to change the way I'm pulling the date. It doesn't like just a plain old cell reference. Help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct based on date entered in separate field
Yes, that's it. I designed the sheet to calculate based on the calendar
month, but now they want it monthly "as of the date checked", where the user enters a date between the 25th and end of month. Column A is the month (I didn't list that one below, nor the one that calculates the % complete, which is Column E) "T. Valko" wrote: Not sure what you're asking for. If you enter an "as of the date checked" how should this be incorporated into your existing formulas? -- Biff Microsoft Excel MVP "Mercdoodle" wrote in message ... I'm a rusty Excel user, and designed a sheet to report on monthly values "items closed" using Sumproduct as indicated below. Here are my fields on the report tab: Column B: Date checked (data entry cell) Column C: Number Submitted =SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$2000)=1),--(YEAR('Data Entry'!$B$2:$B$2000)=2009)) Column D: Number Completed =SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000=DATE(2009,1,1)),--('Data Entry'!$B$2:$B$2000<=DATE(2009,1,31))) I have now found that they want it reported "as of the date checked" which is sometime between the 25th and last date of the month, depending. I have a data entry field for the date checked, but I must have to change the way I'm pulling the date. It doesn't like just a plain old cell reference. Help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct based on date entered in separate field
Ok, to use that date in your current formulas...
A1 = a user entered date ("as of the date checked") Column C: Number Submitted =SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$2000)=MONTH(A1)),--(YEAR('Data Entry'!$B$2:$B$2000)=YEAR(A1))) Column D: Number Completed =SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000=A1),--('Data Entry'!$B$2:$B$2000<=DATE(YEAR(A1),MONTH(A1)+1,0)) ) -- Biff Microsoft Excel MVP "Mercdoodle" wrote in message ... Yes, that's it. I designed the sheet to calculate based on the calendar month, but now they want it monthly "as of the date checked", where the user enters a date between the 25th and end of month. Column A is the month (I didn't list that one below, nor the one that calculates the % complete, which is Column E) "T. Valko" wrote: Not sure what you're asking for. If you enter an "as of the date checked" how should this be incorporated into your existing formulas? -- Biff Microsoft Excel MVP "Mercdoodle" wrote in message ... I'm a rusty Excel user, and designed a sheet to report on monthly values "items closed" using Sumproduct as indicated below. Here are my fields on the report tab: Column B: Date checked (data entry cell) Column C: Number Submitted =SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$2000)=1),--(YEAR('Data Entry'!$B$2:$B$2000)=2009)) Column D: Number Completed =SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data Entry'!$B$2:$B$2000=DATE(2009,1,1)),--('Data Entry'!$B$2:$B$2000<=DATE(2009,1,31))) I have now found that they want it reported "as of the date checked" which is sometime between the 25th and last date of the month, depending. I have a data entry field for the date checked, but I must have to change the way I'm pulling the date. It doesn't like just a plain old cell reference. Help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get next THU date based on date entered in another cell | Excel Worksheet Functions | |||
Date Calculation (from entered date / 1yr later in next field) | Excel Worksheet Functions | |||
Date Entered +7, unless another field is greater than 0 | Excel Worksheet Functions | |||
New date based on one date field minus minutes in another field | Excel Discussion (Misc queries) | |||
Separate Day and Time from a Date Field | Excel Worksheet Functions |