CountIf Function Help

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" <ycart88@discussions.microsoft.com> wrote in message
news:27541546-4520-482E-836A-D853A3E0AB29@microsoft.com...
> 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" <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")
> > > >
> > > >
> > >
> > >
> > >

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" <ycart88@discussions.microsoft.com> wrote in message
> news:27541546-4520-482E-836A-D853A3E0AB29@microsoft.com...
> > 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" <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")
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>
>

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" <ycart88@discussions.microsoft.com> wrote in message
news:8A55E115-6821-43D0-A87A-EA0A8012391A@microsoft.com...
> 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" <ycart88@discussions.microsoft.com> wrote in message
> > news:27541546-4520-482E-836A-D853A3E0AB29@microsoft.com...
> > > 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" <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")
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >