Help with counting based on two conditions

[harnish]

Using Excel 2000,

I want to count how many customers are actively enrolled in a program
at a given date.

Column A contains the Customer Name
Column B contains Program Start Date
Column C contains Program End Date

If I want to count how many people were on the program from April 1,
1999 to May 31, 2000 which would include those with a start date
before April 1, 1999 but who do not have an end date. I tried

Column D with my begin date criteria (August 1, 1996)
Column E with my end date criteria (March 31, 2001)

Column F to hold my counted start dates with the formula
=COUNTIF(B11:B19, ">="&D11) - COUNTIF(B11:B19, ">"&E11)

Column G to hold my counted end dates with the formula
=COUNTIF(C11:C19, ">="&D11) + COUNTIF(C11:C19, "<"&E11)

Column H to subtract F from G with the formula simple formula of =F11-
G11

I've got something wrong with my formula in Column G as it is
resulting of a count of two discharges yet I only have one discharge
date in my sample data. It should tell me I have five active on
program during my timeframe of 1-Aug-96 and 31-May-00 minus the one
person who was discharged on 31-Mar-99.

My sample data for six customers is as follows:

B11 through B16 start dates
1-Apr-99
1-Apr-99
1-Dec-99
2-Dec-99
2-Jan-00
1-Apr-00

C12 end date of 31-Mar-99, rest are blank

Can anyone suggest an easier/better/correct way to arrive at my
desired results?

Thanks!
Les

 

[Don Guillett]

try using sumproduct
=sumproduct((rngB>=???)*(rngB<??))
you can add other criteria such as
=sumproduct((rnga="Joe")*(rngB>=???)*(rngB<??))

 

[Guest]

Hi

Based on your sample try

=SUMPRODUCT(--(B11:B16>=B11)*(B11:B16<"1/4/04")*(C11:C16=""))

Regards
Peter