| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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") |
|
#2
|
|||
|
|||
|
=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") |
|
#3
|
|||
|
|||
|
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") |
|
#4
|
|||
|
|||
|
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") |
|
#5
|
|||
|
|||
|
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") |
|
#6
|
|||
|
|||
|
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") |
|
#7
|
|||
|
|||
|
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") |
|
#8
|
|||
|
|||
|
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") |
|
#9
|
|||
|
|||
|
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") |
|
#10
|
|||
|
|||
|
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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using countif function to add only a half of a number | Excel Discussion (Misc queries) | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
| Countif Function -Nested | Excel Discussion (Misc queries) | |||
| clock | Excel Worksheet Functions | |||
| How do I use COUNTIF in a SUBTOTAL function to differentiate the . | Excel Worksheet Functions | |||
Linear Mode
