| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count using multiple criteria including text strings
i have a spreadsheet where column b contains the title of the position,
column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
se7098 wrote:
i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. Try a PivotTable. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
=SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond,
Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
Thanks for the suggestion Glenn, however, my manager wants to see the results
within the same worksheet. "Glenn" wrote: se7098 wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. Try a PivotTable. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
Thanks for the response, however, i am receiving a #NAME? error
"Teethless mama" wrote: =SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
I think the assumption was that you would have named ranges for "Job_Title",
"Location" and "Candidate". If not, replace those terms in the formula with the actual ranges of the appropriate data. se7098 wrote: Thanks for the response, however, i am receiving a #NAME? error "Teethless mama" wrote: =SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
se7098 wrote:
Thanks for the suggestion Glenn, however, my manager wants to see the results within the same worksheet. If the other solution offered doesn't work for you, then consider using subtotals (at each change in "Job Title", use function "Count" and add subtotal to "Job Title"). |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
Thanks Glenn...i must be missing something...below is my formula...and now i
am receiving a #NUM! error...what am i doing wrong? =SUMPRODUCT((B:B="Sr. Marketing Mgr.")*(C:C="Redmond, Wa")*(D:D<"")) "Glenn" wrote: I think the assumption was that you would have named ranges for "Job_Title", "Location" and "Candidate". If not, replace those terms in the formula with the actual ranges of the appropriate data. se7098 wrote: Thanks for the response, however, i am receiving a #NAME? error "Teethless mama" wrote: =SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
If you are using a version of Excel prior to 2007, I don't think you can
reference the whole columns. se7098 wrote: Thanks Glenn...i must be missing something...below is my formula...and now i am receiving a #NUM! error...what am i doing wrong? =SUMPRODUCT((B:B="Sr. Marketing Mgr.")*(C:C="Redmond, Wa")*(D:D<"")) "Glenn" wrote: I think the assumption was that you would have named ranges for "Job_Title", "Location" and "Candidate". If not, replace those terms in the formula with the actual ranges of the appropriate data. se7098 wrote: Thanks for the response, however, i am receiving a #NAME? error "Teethless mama" wrote: =SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
Ok...so i highlighted the whole column and renamed the columns as a range and
inserted those titles in place of the whole column reference as follows: =SUMPRODUCT((JobTitle="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(CandidateName<"")) still getting the num error. i even tried just searching on about 20 rows in each column and am still getting the error. "Glenn" wrote: If you are using a version of Excel prior to 2007, I don't think you can reference the whole columns. se7098 wrote: Thanks Glenn...i must be missing something...below is my formula...and now i am receiving a #NUM! error...what am i doing wrong? =SUMPRODUCT((B:B="Sr. Marketing Mgr.")*(C:C="Redmond, Wa")*(D:D<"")) "Glenn" wrote: I think the assumption was that you would have named ranges for "Job_Title", "Location" and "Candidate". If not, replace those terms in the formula with the actual ranges of the appropriate data. se7098 wrote: Thanks for the response, however, i am receiving a #NAME? error "Teethless mama" wrote: =SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
Not sure what the problem is. Make sure that you are not referencing the whole
column (named or not) and that all ranges are the same size. se7098 wrote: Ok...so i highlighted the whole column and renamed the columns as a range and inserted those titles in place of the whole column reference as follows: =SUMPRODUCT((JobTitle="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(CandidateName<"")) still getting the num error. i even tried just searching on about 20 rows in each column and am still getting the error. "Glenn" wrote: If you are using a version of Excel prior to 2007, I don't think you can reference the whole columns. se7098 wrote: Thanks Glenn...i must be missing something...below is my formula...and now i am receiving a #NUM! error...what am i doing wrong? =SUMPRODUCT((B:B="Sr. Marketing Mgr.")*(C:C="Redmond, Wa")*(D:D<"")) "Glenn" wrote: I think the assumption was that you would have named ranges for "Job_Title", "Location" and "Candidate". If not, replace those terms in the formula with the actual ranges of the appropriate data. se7098 wrote: Thanks for the response, however, i am receiving a #NAME? error "Teethless mama" wrote: =SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
changed it to:
=SUMPRODUCT((B2:B28="Sr. Marketing Mgr.")*(C2:C28="Redmond, Wa")*(D2:D28<"")) now i am getting a circular reference error. I guess i am missing something...just don't know what. Thanks for all of your help. "Glenn" wrote: Not sure what the problem is. Make sure that you are not referencing the whole column (named or not) and that all ranges are the same size. se7098 wrote: Ok...so i highlighted the whole column and renamed the columns as a range and inserted those titles in place of the whole column reference as follows: =SUMPRODUCT((JobTitle="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(CandidateName<"")) still getting the num error. i even tried just searching on about 20 rows in each column and am still getting the error. "Glenn" wrote: If you are using a version of Excel prior to 2007, I don't think you can reference the whole columns. se7098 wrote: Thanks Glenn...i must be missing something...below is my formula...and now i am receiving a #NUM! error...what am i doing wrong? =SUMPRODUCT((B:B="Sr. Marketing Mgr.")*(C:C="Redmond, Wa")*(D:D<"")) "Glenn" wrote: I think the assumption was that you would have named ranges for "Job_Title", "Location" and "Candidate". If not, replace those terms in the formula with the actual ranges of the appropriate data. se7098 wrote: Thanks for the response, however, i am receiving a #NAME? error "Teethless mama" wrote: =SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond, Wa")*(Candidate<"")) "se7098" wrote: i have a spreadsheet where column b contains the title of the position, column c contains the location and column d contains the name of the candidate. i need to count the number of candidates based on job title and location...each candidate has a unique name so i'm not sure how to make this happen. Staffing Mgr. Job Title Location Candidate Name Clare Duffin Sr. Marketing Mgr. Redmond, Wa Jesse Money Clare Duffin Sr. Marketing Mgr. Redmond, Wa Frank Kemery Clare Duffin Sr. Marketing Mgr. Redmond, Wa Chuck Mildes Clare Duffin Sr. Marketing Mgr. Redmond, Wa Kathleen Strom Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Schutte Clare Duffin Sr. Marketing Mgr. Redmond, Wa Perry Anthony Clare Duffin Sr. Marketing Mgr. Redmond, Wa Susan Nguyen Sr. Marketing Mgr. Total Clare Duffin Sr. Content Mgr. Redmond, Wa Daniel Nolta Clare Duffin Sr. Content Mgr. Redmond, Wa Eric Neuman Clare Duffin Sr. Content Mgr. Redmond, Wa Jenni Grant Sr. Content Mgr. Total Is this possible? Thanks in advance for your help. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
What cell is the formula in? It can't be in B2:B28, C2:C28 or D2:D28.
Did you try subtotals, per my other suggestion (after PivotTable)? se7098 wrote: changed it to: =SUMPRODUCT((B2:B28="Sr. Marketing Mgr.")*(C2:C28="Redmond, Wa")*(D2:D28<"")) now i am getting a circular reference error. I guess i am missing something...just don't know what. Thanks for all of your help. "Glenn" wrote: Not sure what the problem is. Make sure that you are not referencing the whole column (named or not) and that all ranges are the same size. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count using multiple criteria including text strings
I moved the formula to I28...now i am getting a zero which is incorrect.
would subtotals work since i am looking at multiple criteria? i am first looking for job title, then location, then count the number of candidates who have applied for each position. the same job title can be in multiple locations. Thanks for your patience and help. "Glenn" wrote: What cell is the formula in? It can't be in B2:B28, C2:C28 or D2:D28. Did you try subtotals, per my other suggestion (after PivotTable)? se7098 wrote: changed it to: =SUMPRODUCT((B2:B28="Sr. Marketing Mgr.")*(C2:C28="Redmond, Wa")*(D2:D28<"")) now i am getting a circular reference error. I guess i am missing something...just don't know what. Thanks for all of your help. "Glenn" wrote: Not sure what the problem is. Make sure that you are not referencing the whole column (named or not) and that all ranges are the same size. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i count specific text strings excluding blank cells? | Excel Worksheet Functions | |||
| How do I count frequency based on 2 criteria (including month) | Excel Worksheet Functions | |||
| Advanced Filter for multiple criteria, including blank cells | Excel Worksheet Functions | |||
| Count Cells That Don't Contain the Following Text Strings | Excel Discussion (Misc queries) | |||
| count text strings in an entire workbook | Excel Worksheet Functions | |||
Linear Mode
