Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SumProduct/Date Ranges
This statement returns the correct result for records meeting these
conditions and dated before 11/8/2004: =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495<DATE(2004,11,8))) This statement returns 0 for the number of records meeting the conditions and dated between 11/8 and 11/12 inclusive. I know there are 23 such records. =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495=(2004/11/8))*($E$2:$E$2495<=(2004/11/12))) What am I doing wrong? thanks |
#2
|
|||
|
|||
You left out the DATE to identify your dates. I didn't notive anything else
at first glance ... Try: =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495=DATE(2004/11/8))*($E$2:$E$2495<=DATE(2004/11/12))) tj "wal50" wrote: This statement returns the correct result for records meeting these conditions and dated before 11/8/2004: =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495<DATE(2004,11,8))) This statement returns 0 for the number of records meeting the conditions and dated between 11/8 and 11/12 inclusive. I know there are 23 such records. =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495=(2004/11/8))*($E$2:$E$2495<=(2004/11/12))) What am I doing wrong? thanks |
#3
|
|||
|
|||
I neglected to changes the slashes to commas in the DATE funtion sections of
the new function. See below. =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495=DATE(2004,11,8))*($E$2:$E$2 495<=DATE(2004,11,12))) tj "tjtjjtjt" wrote: You left out the DATE to identify your dates. I didn't notive anything else at first glance ... Try: =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495=DATE(2004/11/8))*($E$2:$E$2495<=DATE(2004/11/12))) tj "wal50" wrote: This statement returns the correct result for records meeting these conditions and dated before 11/8/2004: =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495<DATE(2004,11,8))) This statement returns 0 for the number of records meeting the conditions and dated between 11/8 and 11/12 inclusive. I know there are 23 such records. =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495=(2004/11/8))*($E$2:$E$2495<=(2004/11/12))) What am I doing wrong? thanks |
#4
|
|||
|
|||
=SUMPRODUCT(--($G$2:$G$2495="no"),-($A$2:$A$2495="Colin
james"),--($E$2:$E$2495=--("2004/11/08")),--($E$2:$E$2495<=--("2004/11/12") )) -- HTH RP (remove nothere from the email address if mailing direct) "wal50" wrote in message ... This statement returns the correct result for records meeting these conditions and dated before 11/8/2004: =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495<DATE(2004,11,8))) This statement returns 0 for the number of records meeting the conditions and dated between 11/8 and 11/12 inclusive. I know there are 23 such records. =SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin james")*($E$2:$E$2495=(2004/11/8))*($E$2:$E$2495<=(2004/11/12))) What am I doing wrong? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Poor Workbook Performance due to Named Ranges | Excel Discussion (Misc queries) | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
Problem with graph ranges | Charts and Charting in Excel | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |