CountIf Function Help

[Guest]

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

 

[Bob Phillips]

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

I think

 

[Guest]

I'm afraid all this returns is a zero...

But thank you!

 

[Bob Phillips]

I assumed that 'BLDR LEAD RPT'!A4 holds the test value, such as ASH. Is that
correct?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Yes, you assumed correctly.

I assumed that 'BLDR LEAD RPT'!A4 holds the test value, such as ASH. Is that
correct?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Maybe just a slight modification to Bob's formula (changes the first range to
column C from column A)

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

 

[Guest]

Thanks, Duke and Bob! You guys are the best! Say, if I had a date column
and wanted to narrow down the YTD field to MTD, would you happen to know what
I could add to this formula?

THANKS again!!!
Tracy

 

[Bob Phillips]

Do you mean the figures for a certain month? If so then use something like

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

 

[Guest]

Bob -

You're awesome. I can't tell you how much I appreciate all of your help on
this!! Here's what ended up working for me in the end:

=SUMPRODUCT(--(TEXT(LEADS!A2:A2528,"mmm")="May"),--(LEADS!C2:C2528='BLDR
LEAD RPT'!A3),--(LEADS!Y2:Y2528="Y"))

Thanks again!
Tracy

 

[Bob Phillips]

My pleasure Tracy. Just remember it is short form Month with that formula
(Jan, Feb., etc.), seeing as May is short and long form

Bob