Assistance with array formula please

[Iahw]

Hiya,

I'm trying to calculate monthly outputs for a given set of people.
The sheet (Main) contains the Name (column E) and the Date (column G).
Users enter all the main details onto the 'Main' sheet. What I need to
do is to be able to calculate the number of times a users' name
appears during a certain period (Date1,Date2). This formula:

=AND((Main!G2>=MIN("02/04/04","07/05/04")),Main!G2<=MAX("02/04/04","07/05/04"))

works (TRUE) for one cell reference only, but as soon as I add a range
to the formula:

=AND((Main!G2:G401>=MIN("02/04/04","07/05/04")),Main!G2:G401<=MAX("02/04/04","07/05/04"))

the result is FALSE. If I try and add another AND section to reflect
the name I'm looking for:

=AND((Main!G2:G401>=MIN(Date1,Date2)),Main!G2:G401<=MAX(Date1,Date2),Main!E2:E401="Smith,
S")

OR

=IF(AND((Main!G2:G401>=MIN(Date1,Date2)),Main!G2:G401<=MAX(Date1,Date2),Main!E2:E401="Smith,
S"),1,"")

that makes no difference. I've got a feeling that array formulas are
the answer here? but for the life of me I can't seem to get this to
work. All info and help in resolving this will be greatly appreciated.

Thanks

 

[Don Guillett]

try
=sumproduct((a2:a22="joe")*(b2:b22>=c1)*(b2:b22<c2))