Number of staff in particular location

[adn4n]

Hi as you can see from the attachment that there is a employee table, i
do not know how to work out the number of staff in a particular
location. Example how many people are in the location 'London' working
for the company. How do i then create a graph from this, any
suggestions(???)

Attachment filename: employee location.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=520161

 

[Bob Phillips]

Haven't looked at the attachment, but try something like

=COUNTIF(A1:A1000,"London")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Anders S]

- In your example file, enter the locations, Bradford...Winchester, in A31:A39.
- In B31, enter =COUNTIF(E:E,A31) and fill down to B39.
- To create a chart, select A31:B31, then Insert>Chart

In the real application you probably want to choose another area for the new table.

HTH
Anders Silven

 

[DDM]

adn4n, another way: Use a Pivot Table.

1-In your example file, select B7:E27.
2-Data > PivotTable and PivotChart Report.
3-Source = Microsoft Excel List; Kind of Report = PivotChart Report. Click
Next.
4-Where is the data? Ensure that it says B7:E27. Click Next.
5-Where do you want to put it? New Worksheet. Click Layout.
6-Drag the Location tile to the Row area. Drag the Employee # tile to the
Data area. Double-click Employee # tile and select Count. Then OK. Click OK
again. Click Finish.

Excel will insert a Worksheet to the left of the one you're on. It will show
the number of employees by location. Excel will also insert a chart sheet
showing the same data.

I'm using Excel 2003. If your version doesn't support this, just follow the
steps above to create a Pivot Table only, then select the data in the pivot
table and use the Chart Wizard to create your chart.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com