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!