SUMPRODUCT with Date() -60

[David]

Hello all,
I have constructed this
=SUMPRODUCT(--('Sites_A-K'!G:G=A2),--('Sites_A-K'!I:I< (date()-60)))

in my attempts to count the rows where column 'Sites_A-K'!G:G = A2 and
column 'Sites_A-K'!I:I is less than today minus 60 days.

Cell A2 is text. Column G is text. Column I is date format.

I'm not getting an error, the cursor goes to the date() section of the
formula. I have changed date() to today() and getting a #NUM! error.

Any assistance you can provide is greatly appreciated.

 

[T. Valko]

Unless you're using Excel 2007 you can't use entire columns as range
references in SUMPRODUCT. Use a smaller range.

=SUMPRODUCT(--('Sites_A-K'!G1:G1000=A2),--('Sites_A-K'!I1:I1000<TODAY()-60))

However, if you *absolutely need* to reference the entire column try it like
this:

=SUMPRODUCT(--('Sites_A-K'!G1:G65535=A2),--('Sites_A-K'!I1:I65535<TODAY()-60))+('Sites_A-K'!G65536=A2)*('Sites_A-K'!I65536<TODAY()-60)

That'll be one slow to calculate formula!

 

[David]

Works well now, thank you.

Unless you're using Excel 2007 you can't use entire columns as range
references in SUMPRODUCT. Use a smaller range.

=SUMPRODUCT(--('Sites_A-K'!G1:G1000=A2),--('Sites_A-K'!I1:I1000<TODAY()-60))

However, if you *absolutely need* to reference the entire column try it like
this:

=SUMPRODUCT(--('Sites_A-K'!G1:G65535=A2),--('Sites_A-K'!I1:I65535<TODAY()-60))+('Sites_A-K'!G65536=A2)*('Sites_A-K'!I65536<TODAY()-60)

That'll be one slow to calculate formula!

 

[T. Valko]

Ooops!

I just realized that empty cells will evaluate to be TRUE in:

'Sites_A-K'!I1:I65535<TODAY()-60

If there will not be empty cells in that range then it's not a problem. If
there might be empty cells in that range then you'd need to add another
expression to the formula.

So, if you *don't need* to reference the entire column don't do it!

If you don't know where the range will end then use a dynamic range:

http://contextures.com/xlNames01.html#Dynamic