+ Reply to Thread
Results 1 to 3 of 3

multiple criteria with countif or sumif

  1. #1
    Renee
    Guest

    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

  2. #2
    Bernie Deitrick
    Guest

    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
    basis of your pivot table.

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




  3. #3
    Don Guillett
    Guest

    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,
    > 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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