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?

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

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?

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?

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?