|
CountIf Function Help
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") |
=SUMPRODUCT(--(LEADS!A2:A599='BLDR LEAD RPT'!A4),--(LEADS!Y2:Y599="Y"))
I think :-) -- HTH Bob Phillips "ycart88" wrote in message ... 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") |
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" wrote in message ... 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") |
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" wrote in message ... 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" wrote in message ... 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") |
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" wrote in message ... 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" wrote in message ... 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") |
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" wrote in message ... 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") |
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" wrote in message ... 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") |
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" wrote in message ... 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" wrote in message ... 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 -
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" wrote in message ... 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" wrote in message ... 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") |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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") |
| All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com