sumproduct with date range

  • Thread starter Thread starter joemeshuggah
  • Start date Start date
J

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?
 
Hi

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

--
Regards
Roger Govier

joemeshuggah said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
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)
 
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.
 
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!
 
Back
Top