sumproduct with date range

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

Roger Govier

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

__________ 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

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)
 
J

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.
 
J

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top