multiple criteria with countif or sumif

1. 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

See Ex.
Worksheet #1

Location Name Title
CALV Smith, A. Laborer
PUL Simms, F. Chef II
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

2. Re: 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

HTH,
Bernie
MS Excel MVP

"Renee" <Renee@discussions.microsoft.com> wrote in message
news:9BE68FA0-BA01-41B5-BEF4-632217964121@microsoft.com...
>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
>
> 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

3. Re: 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
dguillett1@austin.rr.com
"Renee" <Renee@discussions.microsoft.com> wrote in message
news:9BE68FA0-BA01-41B5-BEF4-632217964121@microsoft.com...
>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,
> 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
>
> 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1