Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 27th 04, 03:55 AM
wal50
 
Posts: n/a
Default 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   Report Post  
Old November 27th 04, 04:17 AM
tjtjjtjt
 
Posts: n/a
Default

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   Report Post  
Old November 27th 04, 04:29 AM
tjtjjtjt
 
Posts: n/a
Default

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   Report Post  
Old November 27th 04, 12:56 PM
Bob Phillips
 
Posts: n/a
Default

=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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Poor Workbook Performance due to Named Ranges jrusso Excel Discussion (Misc queries) 2 January 11th 05 12:39 AM
How to Link named ranges from multiple Workbooks into a single Wo. PMAP_HELP Excel Discussion (Misc queries) 1 December 14th 04 06:51 PM
Problem with graph ranges No Such Luck Charts and Charting in Excel 6 December 3rd 04 02:09 PM
compare unique identifiers in multiple ranges bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 10:23 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017