IF with date criteria
 

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:F20039000))}
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

IF with date criteria
 

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

"DTTODGG" wrote in message
...
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:F20039000))}
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

IF with date criteria
 

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

"DTTODGG" wrote in message
...
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:F20039000))}
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

IF with date criteria
 

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

"Bob Phillips" wrote:

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

"DTTODGG" wrote in message
...
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:F20039000))}
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

IF with date criteria
 

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)

"DTTODGG" wrote in message
...
Thank you for your quick reply.
I get an error using the second formula. Am I missing something?

"Bob Phillips" wrote:

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

"DTTODGG" wrote in message
...
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:F20039000))}
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