count using multiple criteria including text strings

[se7098]

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.

 

[Glenn]

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.

 

[Teethless mama]

=SUMPRODUCT((Job_Title="Sr. Marketing Mgr.")*(Location="Redmond,
Wa")*(Candidate<>""))

 

[se7098]

Thanks for the suggestion Glenn, however, my manager wants to see the results
within the same worksheet.

 

[se7098]

Thanks for the response, however, i am receiving a #NAME? error

 

[Glenn]

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.

 

[Glenn]

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

 

[se7098]

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,

 

[Glenn]

If you are using a version of Excel prior to 2007, I don't think you can
reference the whole columns.

 

[se7098]

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]

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]

changed it to:

=SUMPRODUCT((B2:B28="Sr. Marketing Mgr.")*(C2:C28="Redmond,
Wa")*(D228<>""))

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]

What cell is the formula in? It can't be in B2:B28, C2:C28 or D228.

Did you try subtotals, per my other suggestion (after PivotTable)?

 

[se7098]

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.