SUMPRODUCT Formula Problem - Dates 1

[alexhu]

I'm using SUMPRODUCT in the following way to compare dates

=SUMPRODUCT((Orange!$A$2:$A$247>0)*1,(Orange!$I$2:$I$247<NOW()-14)*1)

This will give me the number of dates that are 14 days or older than todays date

Good so far!

Now I want to find the number of dates which are 7 days older than today, but not older than 14

Any ideas?

Alex

 

[xlbo]

Hi alexhu - not sure what your * 1 is doing - I would write the initial formula as:

=SUMPRODUCT((Orange!$A$2:$A$247>0)*(Orange!$I$2:$I$247<NOW()-14))

Which makes the formula easier to read (well I think so anyway ;-) )

To do what you require should be pretty straightforward - just an extra step of logic...

=SUMPRODUCT((Orange!$A$2:$A$247>0)*(Orange!$I$2:$I$247<=NOW()-7)*(Orange!$I$2:$I$247>NOW()-14))


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[alexhu]

Thanks for that Geoff - I have no idea what the *1 was doing either, this was given to me by an 'expert' for me to modify

Anyway as it works so well have a star on me

Alex

 

[xlbo]

psst - for future reference, this really should've been posted in the MSOffice forum. There is an excellent FAQ in there that gives an explanation of how SUMPRODUCT works:
faq68-4725


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi alexhu,

The [blue]*1[/blue] is there to coerce the True/False results to numbers on which the SUMPRODUCT can work. Any operation which leaves the results unchanged (bar the coercion) will do, including multiplication by another array as per Geoff's answer.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.