IF with date criteria

[Guest]

Hello,
This is my problem:

Column C has text values (Phone, Meeting)
Column F has dates (01/01/2006 - 08/31/2006)
but, Column F also has misc text ("*", ".", "?" or can be blank)
Column H has text values ("AA", "AA-1", "AA-2", ""BB")

I'm trying to find out how many "Phone" calls between 06/01/2006 and
07/31/2006

I'm trying to find out how many "Phone" calls where in the "AA" family
(includes "AA-1" and "AA-2")

When I was just looking for "Phone" and a number > 39000 this worked:
{=SUM((C2:C200="Phone")*(F2:F200>39000))}
The problem was I couldn't specify a date range, and it counted the other
characters.

Thank-you,
P.S. I learned a lot from the Pearson website

 

[Bob Phillips]

=SUMPRODUCT(--(F2:F200>=--"2006-06-01"),--(F2:F200<=--"2006-07-32"))

=SUMPRODUCT--(LEFT(H2:H200,2)="AA"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Umlas]

=SUMPRODUCT(--(C2:C200="Phone"),--(F2:F200>=DATEVALUE("01/01/2006")),--(F2:F
200<=DATEVALUE("08/31/2006")))

 

[Guest]

Thank you for your quick reply.
I get an error using the second formula. Am I missing something?

 

[Bob Phillips]

Yes a bracket, because I didn't give it to you <g>

=SUMPRODUCT(--(LEFT(H2:H200,2)="AA"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)