SUMPRODUCT Issue

[Stuck]

I define a range as a name (RQTS). Within that range is a set of random
dates. I would like to know if the dates are greater than one date and less
than another date. For instance:

A2 = 8/1/08
B2 = 8/31/08
C2:C500 = Defined name (RQTS)--random set of dates. Here is my formula. It
is returning 0 every time. What am I doing wrong? There should be 6
instances that are counted.

=SUMPRODUCT((RQTS>$A2)*(RQTS<$B2))

 

[Max]

My hunch would be that the source "dates" aren't real dates recognized by Excel

But your expression should be, to be precise:
=SUMPRODUCT((RQTS>=$A2)*(RQTS<=$B2))

Alternatively, I'd use this expression
when capturing dates which fall within a certain month/year:
=SUMPRODUCT(--(TEXT(RQTS,"mmmyy")="Aug08"))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Max]

To convert all "dates" in RQTS into real dates at one go, use Data>Text to
Columns. In step 3 of the wizard, check "date", then select the appropriate
format from the droplist, eg: MDY. Click Finish.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Stuck]

Ok, let me simplify it a bit because I am still not getting accurate results.
I have various dates in column J. I have a beginning date (constant) in A2
and an end date (constant) in B2. I want column J to be counted if it is
greater than A2 and less than B2. This should be simple, but I am getting
only 0's.

I've tried this formula...doesn't work.

=SUMPRODUCT(--(J21:J107>A2),--(J21:J107<B2))

 

[Max]

It's not that your earlier expression, or the latest one that you're now
trying (as you post below) are flawed, except for the imprecise limits
capture (I still think you should use: >=, <= if you're defining it to
capture for a particular month/year). It is the "dates" source data that you
need to re-check/rectify to ensure that these are real dates. You should also
double check that the start/end date limits that you define in A2:B2 are real
dates recognized by Excel.

ALL of the expressions posted in this thread should work.
It's your "dates" data quality
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Ashish Mathur]

Hi,

Did you try the text to columns as suggested by MAX

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com