CountIf Function Help

ycart88 · 05-16-2005, 02:06 PM

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 · 05-16-2005, 02:06 PM

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

I think :-)

--
HTH

Bob Phillips

"ycart88" <[email protected]> wrote in message
news:[email protected]...
> 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")
>
>

ycart88 · 05-16-2005, 03:06 PM

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

But thank you!

"Bob Phillips" wrote:

> =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD RPT'!A4),--(LEADS!Y2:Y599="Y"))
>
> I think :-)
>
> --
> HTH
>
> Bob Phillips
>
> "ycart88" <[email protected]> wrote in message
> news:[email protected]...
> > 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 · 05-16-2005, 04:06 PM

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)

"ycart88" <[email protected]> wrote in message
news:[email protected]...
> I'm afraid all this returns is a zero...

>
> But thank you!
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD RPT'!A4),--(LEADS!Y2:Y599="Y"))
> >
> > I think :-)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "ycart88" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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")
> > >
> > >
> >
> >
> >

ycart88 · 05-16-2005, 04:06 PM

Yes, you assumed correctly.

"Bob Phillips" wrote:

> 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)
>
>
> "ycart88" <[email protected]> wrote in message
> news:[email protected]...
> > I'm afraid all this returns is a zero...

> >
> > But thank you!
> >
> >
> > "Bob Phillips" wrote:
> >
> > > =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD RPT'!A4),--(LEADS!Y2:Y599="Y"))
> > >
> > > I think :-)
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "ycart88" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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")
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Duke Carey · 05-16-2005, 05:06 PM

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

"Bob Phillips" wrote:

> =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD RPT'!A4),--(LEADS!Y2:Y599="Y"))
>
> I think :-)
>
> --
> HTH
>
> Bob Phillips
>
> "ycart88" <[email protected]> wrote in message
> news:[email protected]...
> > 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")
> >
> >
>
>
>

ycart88 · 05-16-2005, 07:06 PM

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

"Duke Carey" wrote:

> 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"))
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD RPT'!A4),--(LEADS!Y2:Y599="Y"))
> >
> > I think :-)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "ycart88" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 · 05-18-2005, 05:06 AM

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

--
HTH

Bob Phillips

"ycart88" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Duke Carey" wrote:
>
> > 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"))
> >
> >
> > "Bob Phillips" wrote:
> >
> > > =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD
RPT'!A4),--(LEADS!Y2:Y599="Y"))
> > >
> > > I think :-)
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "ycart88" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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")
> > > >
> > > >
> > >
> > >
> > >

ycart88 · 05-18-2005, 04:06 PM

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" wrote:

> 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"))
>
>
> --
> HTH
>
> Bob Phillips
>
> "ycart88" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Duke Carey" wrote:
> >
> > > 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"))
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD
> RPT'!A4),--(LEADS!Y2:Y599="Y"))
> > > >
> > > > I think :-)
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "ycart88" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 · 05-18-2005, 07:06 PM

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

"ycart88" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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"))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "ycart88" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > > "Duke Carey" wrote:
> > >
> > > > 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"))
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > =SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD
> > RPT'!A4),--(LEADS!Y2:Y599="Y"))
> > > > >
> > > > > I think :-)
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "ycart88" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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")
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >