counting
 

I would like to count specific transactions in progress on specific dates.
Each transaction will have different start and closing dates. For example,
1/1/05 may be a start date with 1/5/05 its close; and another starts 1/2/05
with 1/15/05 its close, thus on 1/3/05 I would have two open transactions.
Each row in my sheet is a separate transaction and a column is dedicated for
the open and one for closing date. I have apx. 39,000 transactions to count
over a ten year period.

=SUMPRODUCT(--(A1:A40000<=--"2005/01/03"),--(B1:B40000=--"2005/01/03"))

or you could use

=SUMPRODUCT(--(A1:A40000<=C1),--(B1:B40000=C1))

if the date is in C1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Russell" wrote in message
...
I would like to count specific transactions in progress on specific dates.
Each transaction will have different start and closing dates. For
example,
1/1/05 may be a start date with 1/5/05 its close; and another starts
1/2/05
with 1/15/05 its close, thus on 1/3/05 I would have two open transactions.
Each row in my sheet is a separate transaction and a column is dedicated
for
the open and one for closing date. I have apx. 39,000 transactions to
count
over a ten year period.

and for details on how the SUMPRODUCT function works check out

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A1:A40000<=--"2005/01/03"),--(B1:B40000=--"2005/01/03"))

or you could use

=SUMPRODUCT(--(A1:A40000<=C1),--(B1:B40000=C1))

if the date is in C1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Russell" wrote in message
...
I would like to count specific transactions in progress on specific
dates.
Each transaction will have different start and closing dates. For
example,
1/1/05 may be a start date with 1/5/05 its close; and another starts
1/2/05
with 1/15/05 its close, thus on 1/3/05 I would have two open
transactions.
Each row in my sheet is a separate transaction and a column is dedicated
for
the open and one for closing date. I have apx. 39,000 transactions to
count
over a ten year period.