Best Way To Organise Headcounts?

    Hi there,


    More general advice and formula names needed really.


    I've been tasked with organising a spreadsheet that contains several thousand staff members.


    Now there's three key columns we need both information from and summarising of.


    Name - Team - Division - City


    That's moreorless how it looks - now we need to have another spreadsheet that's grabbing the headcounts for every level of this and this is where my questions come in:


    1) Say I want to count the number of people working in Boston - I'd just do Countif(d2:d4500, "Boston")


    However this means I need to know each city and manually go through the list - can anyone think of a smart way to scan the D column and list each city only once per row in another spreadsheet and then add the number of staff?


    2) Say if I want to subgroup - ie I will need to know how many people work in the Admin Team, in the HR division, in New York. How could I


    a) do this as a formula (I need to be able to say Countif(x) AND Countif(y) AND Countif(z)or something)


    b) Organise this dynamically - ie so if I added a new team within the HR division in New York, it'd automatically add the team name and headcount...

    Many thanks for any help you can provide - you've been superb in the past!

    Re: Best Way To Organise Headcounts?


    omnikron32,


    1) To get the list of unique cities, use the [Data][Filter][Advanced Filter...] command from the Excel menus. After you have the list, you can write the formula to refer to the cell the city name is in instead of naming the city in the formula.


    =Countif(d2:d4500,a2) (Where a2 is the location of the cell with "Boston" in it)


    2)(a) To countif under multiple conditions you'll probably need to use sumproduct.


    =sumproduct((a2=$d$2:$d$45)*(b2=$e$2:$e$45)*(c2=$f$2:$f$45)) (Where a2, b2, c2 are cells holding the unique combinations of teams, divisions, and cities)


    2)(b) To do this automatically, you'll need to plug some really fancy formulas into a preselected area of an unused sheet (post your example and I'll bet we can do it) or you'll need a macro.


    Jim

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login