| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct with date range
i am trying to sum column i on a particular worksheet where the date in
column f is greater than or equal to the beginning of the month. the formula i have is as follows... =SUMPRODUCT(--'Jan-Jun'!I2:I65536,--'Jan-Jun'!F:F=(TODAY()-(DAY(TODAY())-1))) the result is a #num! error...where is my formula incorrect? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with date range
Hi
Try =SUMPRODUCT(--('Jan-Jun'!F2:F65536=(TODAY()-DAY(TODAY())-1)),'Jan-Jun'!I2:I65536) -- Regards Roger Govier "joemeshuggah" wrote in message ... i am trying to sum column i on a particular worksheet where the date in column f is greater than or equal to the beginning of the month. the formula i have is as follows... =SUMPRODUCT(--'Jan-Jun'!I2:I65536,--'Jan-Jun'!F:F=(TODAY()-(DAY(TODAY())-1))) the result is a #num! error...where is my formula incorrect? __________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with date range
Are you using Excel 2007? If not, then you can't use entire columns as range
references in SUMPRODUCT. I'd use a cell to hold the date criteria. A1 = some date like 1/1/2010 Then: =SUMIF('Jan-Jun'!F:F,"="&A1,'Jan-Jun'!I:I) -- Biff Microsoft Excel MVP "joemeshuggah" wrote in message ... i am trying to sum column i on a particular worksheet where the date in column f is greater than or equal to the beginning of the month. the formula i have is as follows... =SUMPRODUCT(--'Jan-Jun'!I2:I65536,--'Jan-Jun'!F:F=(TODAY()-(DAY(TODAY())-1))) the result is a #num! error...where is my formula incorrect? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with date range
First issue is that sumproduct does not like references to entire columns
(prior to 2007) and even if it did your ranges are not equal in size. (--'Jan-Jun'!I2:I65536,--'Jan-Jun'!F2:F65536=( I did not check your logic to confirm that it will return the correct amount. -- HTH... Jim Thomlinson "joemeshuggah" wrote: i am trying to sum column i on a particular worksheet where the date in column f is greater than or equal to the beginning of the month. the formula i have is as follows... =SUMPRODUCT(--'Jan-Jun'!I2:I65536,--'Jan-Jun'!F:F=(TODAY()-(DAY(TODAY())-1))) the result is a #num! error...where is my formula incorrect? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct with date range
i totally missed that i forgot to put the row numbers in the range reference
in the second portion of the equation...works fine after the change...thanks! "Jim Thomlinson" wrote: First issue is that sumproduct does not like references to entire columns (prior to 2007) and even if it did your ranges are not equal in size. (--'Jan-Jun'!I2:I65536,--'Jan-Jun'!F2:F65536=( I did not check your logic to confirm that it will return the correct amount. -- HTH... Jim Thomlinson "joemeshuggah" wrote: i am trying to sum column i on a particular worksheet where the date in column f is greater than or equal to the beginning of the month. the formula i have is as follows... =SUMPRODUCT(--'Jan-Jun'!I2:I65536,--'Jan-Jun'!F:F=(TODAY()-(DAY(TODAY())-1))) the result is a #num! error...where is my formula incorrect? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct of date range | Excel Worksheet Functions | |||
| SUMPRODUCT with Date Range | Excel Worksheet Functions | |||
| Sumproduct on date range | Excel Discussion (Misc queries) | |||
| Can a date range be used in SUMPRODUCT | Excel Worksheet Functions | |||
| SUMPRODUCT between date range | New Users to Excel | |||
Linear Mode
