counting

[Guest]

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.

 

[Bob Phillips]

=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)

 

[JulieD]

and for details on how the SUMPRODUCT function works check out

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