=SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD RPT'!A4),--(LEADS!Y2:Y599="Y"))

I think :-)

--
HTH

Bob Phillips

"ycart88" <ycart88@discussions.microsoft.com> wrote in message
news:E4BF6D9E-5FA0-43D8-93AC-2F1E7404C866@microsoft.com...
> Hi all,
>
> I'm been trying to figure this out for days... I need to take the
following
> sales lead information and plug some data from it into the summary page as
> shown below...
>
> DATA EXAMPLE (Sales Leads):
>
> LEAD DATE SOURCE HOMEBUILDER ... APPT SET
> (Column A) (Column B) (Column C) ... (Column Y)
> 3/28/05 Homebuilder ASH N
> 3/28/05 Homebuilder ASH Y
>
> SUMMARY EXAMPLE:
> Builder YTD Leads YTD Appts
> ASH 43
> CAC 33
> CAL 8
> COR 0
>
> So basically, if the Homebuilder "ASH" has had a total of 6 Appts Set
("Y"),
> I'd like to put a count in the corresponding YTD Appts cell. Here's how
far
> I've gotten, and yes, I'm aware it's very flawed as I can only figure out
how
> to count ALL of the "Y"'s and not just the ones for that builder ("ASH").
> lol Can you help?
>
> =COUNTIF(LEADS!C2:C599,'BLDR LEAD RPT'!A4) - COUNTIF(LEADS!Y2:Y599,"N")
>
>