multiple criteria with countif or sumif
 

I have a worksheet that lists employees, their locations & job titles. On
another worksheet I need a formula that counts the employees from each
locations dependent upon what group they fall in, ie. (mgmt, Administrative,
labor, etc.). The formula below works, but since I have so much criteria, I
run out of space.

Formula:
{=COUNT(IF(('Employees Works Sheet'!B1:B750="CALV ")*('Employees Work
Sheet'!D1:D750="Chauffeur I"),'Employees Work
Sheet'!A1:A750))+COUNT(IF(('Employees Work Sheet'!B9:B750="CALV")*('Employees
Work Sheet'!D1:D750="Laborer"),'Employees Work
Sheet'!A1:a750))+COUNT(IF(('Employees Work Sheet'!B9:B750="Crew
Leader"),'Employees Work Sheet'!A1:A750))}

See Ex.
Worksheet #1

Location Name Title
CALV Smith, A. Laborer
PUL Simms, F. Chef II
CALV Watson, T. Crew Leader
KEY HWY Jackson, G. Crew Leader

Worksheet #2
Location MGT CLERICAL LABOR
CALV
Formula results (2)
PUL
KEY HWY
MID-TOWN

Any help would be greatly appreciated

multiple criteria with countif or sumif
 

Renee,

Forget formulas. Use a Pivot Table. Select your data table, then use Data / Pivot Table.... and
click Ok when the dialog comes up. Then drag the location to the row field, the Job type to the
column field, and Job type to the data field, and you will get a summary table of job type by
location. If you want further categorization of the jobs, you could use another column to identify
the type of job, with entries such as Management , Clerical, Labor, etc. and then use that as the
basis of your pivot table.

HTH,
Bernie
MS Excel MVP


"Renee" wrote in message
...
I have a worksheet that lists employees, their locations & job titles. On
another worksheet I need a formula that counts the employees from each
locations dependent upon what group they fall in, ie. (mgmt, Administrative,
labor, etc.). The formula below works, but since I have so much criteria, I
run out of space.

Formula:
{=COUNT(IF(('Employees Works Sheet'!B1:B750="CALV ")*('Employees Work
Sheet'!D1:D750="Chauffeur I"),'Employees Work
Sheet'!A1:A750))+COUNT(IF(('Employees Work Sheet'!B9:B750="CALV")*('Employees
Work Sheet'!D1:D750="Laborer"),'Employees Work
Sheet'!A1:a750))+COUNT(IF(('Employees Work Sheet'!B9:B750="Crew
Leader"),'Employees Work Sheet'!A1:A750))}

See Ex.
Worksheet #1

Location Name Title
CALV Smith, A. Laborer
PUL Simms, F. Chef II
CALV Watson, T. Crew Leader
KEY HWY Jackson, G. Crew Leader

Worksheet #2
Location MGT CLERICAL LABOR
CALV
Formula results (2)
PUL
KEY HWY
MID-TOWN

Any help would be greatly appreciated

multiple criteria with countif or sumif
 

try an array within an array
b1:d750={"calv", "chaufer","etc")
if that doesn't work just array within
b1:B and d1:D

--
Don Guillett
SalesAid Software

"Renee" wrote in message
...
I have a worksheet that lists employees, their locations & job titles. On
another worksheet I need a formula that counts the employees from each
locations dependent upon what group they fall in, ie. (mgmt,
Administrative,
labor, etc.). The formula below works, but since I have so much criteria,
I
run out of space.

Formula:
{=COUNT(IF(('Employees Works Sheet'!B1:B750="CALV ")*('Employees Work
Sheet'!D1:D750="Chauffeur I"),'Employees Work
Sheet'!A1:A750))+COUNT(IF(('Employees Work
Sheet'!B9:B750="CALV")*('Employees
Work Sheet'!D1:D750="Laborer"),'Employees Work
Sheet'!A1:a750))+COUNT(IF(('Employees Work Sheet'!B9:B750="Crew
Leader"),'Employees Work Sheet'!A1:A750))}

See Ex.
Worksheet #1

Location Name Title
CALV Smith, A. Laborer
PUL Simms, F. Chef II
CALV Watson, T. Crew Leader
KEY HWY Jackson, G. Crew Leader

Worksheet #2
Location MGT CLERICAL LABOR
CALV
Formula results (2)
PUL
KEY HWY
MID-TOWN

Any help would be greatly appreciated