Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Issue
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)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Issue
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 --- "Stuck" wrote: 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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Issue
To convert all "dates" in RQTS into real dates at one go, use DataText 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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Issue
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:J107A2),--(J21:J107<B2)) "Max" wrote: To convert all "dates" in RQTS into real dates at one go, use DataText 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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Issue
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 --- "Stuck" wrote: 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:J107A2),--(J21:J107<B2)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Issue
Hi,
Did you try the text to columns as suggested by MAX -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Stuck" wrote in message ... 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:J107A2),--(J21:J107<B2)) "Max" wrote: To convert all "dates" in RQTS into real dates at one go, use DataText 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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Issue | Excel Worksheet Functions | |||
I believe this is a SUMPRODUCT issue | Excel Worksheet Functions | |||
SumProduct CountIF issue | Excel Worksheet Functions | |||
Issue with sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT issue | Excel Worksheet Functions |