IF with date criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DTTODGG - 30 Aug 2006 21:12 GMT

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

Reply to this Message

Bob Phillips - 30 Aug 2006 21:21 GMT

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

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

Signature

HTH

Bob Phillips

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

> Hello,
> This is my problem:
[quoted text clipped - 17 lines]
> Thank-you,
> P.S. I learned a lot from the Pearson website

Reply to this Message

DTTODGG - 31 Aug 2006 13:04 GMT

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

> =SUMPRODUCT(--(F2:F200>=--"2006-06-01"),--(F2:F200<=--"2006-07-32"))
>
[quoted text clipped - 21 lines]
> > Thank-you,
> > P.S. I learned a lot from the Pearson website

Reply to this Message

Bob Phillips - 31 Aug 2006 13:16 GMT

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

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

Signature

HTH

Bob Phillips

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

> Thank you for your quick reply.
> I get an error using the second formula. Am I missing something?
[quoted text clipped - 24 lines]
> > > Thank-you,
> > > P.S. I learned a lot from the Pearson website

Reply to this Message

Bob Umlas - 30 Aug 2006 21:26 GMT

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

> Hello,
> This is my problem:
[quoted text clipped - 17 lines]
> Thank-you,
> P.S. I learned a lot from the Pearson website

Reply to this Message