sumproduct with date range

[joemeshuggah]

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?

 

[Roger Govier]

Hi

Try
=SUMPRODUCT(--('Jan-Jun'!F2:F65536>=(TODAY()-DAY(TODAY())-1)),'Jan-Jun'!I2:I65536)

--
Regards
Roger Govier

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

 

[T. Valko]

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)

 

[Jim Thomlinson]

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.

 

[joemeshuggah]

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!