counting problem

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/

Excel487

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.

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

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)

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(1999,12,31)),--(('Al
Data'!C1:C4158)>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