counting problem
Author Message
warmtobe

 Posted: Thu Aug 05 16:19:55 CDT 2004 Top
 worksheet functions >> counting problem Hi, All! My data originates in an Oracle database and is accessible to me vi Access. The three fields I'm interested in are IdNo, Inception (date and Termination (date). What's the best way to count the IdNos that existed at the end of 199 and 2000? I need to do this for termination dates 2000 - 200 (Current.) I have used Query to get the year end list of IdNo, Inception an Termination for 1999 - 2004 and I can filter 1999 against 2000 and not the number of matches, but there seems like there should be a bette way. TIA, Ji -- Message posted from http://www.hide-link.com/ Excel480

Jonathan

 Posted: Thu Aug 05 16:19:55 CDT 2004 Top
 worksheet functions >> counting problem > I have used Query to get the year end list of IdNo, Inception and > Termination for 1999 - 2004 and I can filter 1999 against 2000 and note > the number of matches, but there seems like there should be a better > way. try something like =sumproduct(--(inception1:inception100=1999),--(termination1:termination100 =2000) and replace "inception" with the column of the inception dates, and "termination" with the column of termination dates, and "100" with how many rows there are.

JBoulton

 Posted: Fri Aug 06 15:06:53 CDT 2004 Top
 worksheet functions >> counting problem Jon, Thanks for the idea. The following produces 0.00. =SUMPRODUCT(--('All Data'!C1:C4152=1999),--('All Data'!D1:D4152=2000)) Col C is inception date and col D is termination date. The data i these fields is standard mm/dd/yyyy. I don't understand: 1) how this could catch the idno in col B 2) how this could get from mm/dd/yyyy to 1999 or 200 -- Message posted from http://www.ExcelForum.com

Jonathan

 Posted: Mon Aug 09 10:57:02 CDT 2004 Top
 worksheet functions >> counting problem > Col C is inception date and col D is termination date. The data in > these fields is standard mm/dd/yyyy. Okay, I didn't know that when I wrote it. > I don't understand: > > 1) how this could catch the idno in col B I looked at your message and the only thing I could find you asking for was a straight count. What do you want to do with column B, the ID numbers? Do you want a count, or do you want the actual list? If you want the actual list, then Query, or Data/Filter is the way to go. > 2) how this could get from mm/dd/yyyy to 1999 or 2000 You didn't say how your dates were stored. I assumed you just had the year. So change the formula to say: =sumproduct(--(year('All Data'!C1:C4152)=1999),--(year('All Data'! D1:D4152)=2000)

JBoulton

 Posted: Mon Aug 09 14:22:09 CDT 2004 Top
 worksheet functions >> counting problem Jon, I have already tried changing the formula to year() within th =sumproduct() function. That produces the # VALUE! error. I've also tried <1999 and >2000 and get the same error. This does produce a correct result for the number of IdNos that wer still active at the end of 2000. =SUMPRODUCT(--(('All Data'!B1:B4158)DATE(2000,12,31))) I changed the access query to deliver only IdNo (Col A), inception dat (Col B) and termination date (Col C) I'm trying to count all the IdNos that were active at the end of 199 and still active (not terminated) at the end of 2000. What function would you recommend for getting the total active at th end of 1999? TIA Ji -- Message posted from http://www.ExcelForum.com