software

Hi I have a list of data shown below, in 2 cloumns.

I want to do a count if to show the total people in each department and
location i.e.
COUNT the number of occurances of people in

HEAD OFFICE and PRODUCTION

and then

the number of occurances of people in

HEAD OFFICE and FINANCE

and so on....

Many thanks

Location Department

Head OfficeProduction
BirminghamSales
LondonFinance
Head OfficeFinance
Head OfficeFinance
Head OfficeFinance
Head OfficeFinance
Head OfficeFinance
Head OfficeFinance
Head OfficeFinance
Head OfficeSales
Head OfficeSales
Head OfficeSales
Head OfficeFinance
Head OfficeSales
Head OfficeFinance
LondonProduction
LondonProduction
LondonProduction
LondonProduction
LondonProduction
LondonSales
LondonSales
LondonSales
LondonSales
LondonFinance
LondonFinance
BirminghamProduction
BirminghamProduction
BirminghamProduction
BirminghamProductionA Pivot Table would work nicely here, or use SUMPRODUCT
Let your data be in A1:B100
In D25 (or however many rows it takes) enter the department names
In E1:K1 (or however many columns it takes) enter the location names
In E2 enter =SUMPRODUCT(--($A$1:$A$100=$D2),--($B$1:$B$100=E$1)
copy down and across you table
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Nickquot; gt; wrote in message
...
gt; Hi I have a list of data shown below, in 2 cloumns.
gt;
gt; I want to do a count if to show the total people in each department and
gt; location i.e.
gt; COUNT the number of occurances of people in
gt;
gt; HEAD OFFICE and PRODUCTION
gt;
gt; and then
gt;
gt; the number of occurances of people in
gt;
gt; HEAD OFFICE and FINANCE
gt;
gt; and so on....
gt;
gt; Many thanks
gt;
gt; Location Department
gt;
gt; Head Office Production
gt; Birmingham Sales
gt; London Finance
gt; Head Office Finance
gt; Head Office Finance
gt; Head Office Finance
gt; Head Office Finance
gt; Head Office Finance
gt; Head Office Finance
gt; Head Office Finance
gt; Head Office Sales
gt; Head Office Sales
gt; Head Office Sales
gt; Head Office Finance
gt; Head Office Sales
gt; Head Office Finance
gt; London Production
gt; London Production
gt; London Production
gt; London Production
gt; London Production
gt; London Sales
gt; London Sales
gt; London Sales
gt; London Sales
gt; London Finance
gt; London Finance
gt; Birmingham Production
gt; Birmingham Production
gt; Birmingham Production
gt; Birmingham Production
gt;
With locations in cells A5:A35, and Departments in cells B5:B35, and
assuming an employee count by location/department in cells c5:c35...

And assuming you have a summary section that looks like this in F5:G15
BirminghamFinance
BirminghamProduction
BirminghamSales

Head OfficeFinance
Head OfficeProduction
Head OfficeSales

London Finance
London Production
London Sales

....you can use this formula to summarize:
=SUMPRODUCT(--(F5=$A$5:$A$35),--(G5=$B$5:$B$35),$C$5:$C$35)Hi,
What you need is SUMPRODUCT.

=SUMPRODUCT((A1:A10=quot;head officequot;)*(B1:B10=quot;productionquot;))

HTH
JG

quot;Nickquot; wrote:

gt; Hi I have a list of data shown below, in 2 cloumns.
gt;
gt; I want to do a count if to show the total people in each department and
gt; location i.e.
gt; COUNT the number of occurances of people in
gt;
gt; HEAD OFFICE and PRODUCTION
gt;
gt; and then
gt;
gt; the number of occurances of people in
gt;
gt; HEAD OFFICE and FINANCE
gt;
gt; and so on....
gt;
gt; Many thanks
gt;
gt; Location Department
gt;
gt; Head OfficeProduction
gt; BirminghamSales
gt; LondonFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeSales
gt; Head OfficeSales
gt; Head OfficeSales
gt; Head OfficeFinance
gt; Head OfficeSales
gt; Head OfficeFinance
gt; LondonProduction
gt; LondonProduction
gt; LondonProduction
gt; LondonProduction
gt; LondonProduction
gt; LondonSales
gt; LondonSales
gt; LondonSales
gt; LondonSales
gt; LondonFinance
gt; LondonFinance
gt; BirminghamProduction
gt; BirminghamProduction
gt; BirminghamProduction
gt; BirminghamProduction
gt;

One option is to use a pivot table. Drag Location and Department into the
Row area and Count of Department into the data area and what you should see
is:Count of Department
LocationDepartmentTotal
BirminghamProduction4
Sales1
Birmingham Total5
Head OfficeFinance9
Production1
Sales4
Head Office Total14
LondonFinance3
Production5
Sales4
London Total12
Grand Total31

--
Gary''s Studentquot;Nickquot; wrote:

gt; Hi I have a list of data shown below, in 2 cloumns.
gt;
gt; I want to do a count if to show the total people in each department and
gt; location i.e.
gt; COUNT the number of occurances of people in
gt;
gt; HEAD OFFICE and PRODUCTION
gt;
gt; and then
gt;
gt; the number of occurances of people in
gt;
gt; HEAD OFFICE and FINANCE
gt;
gt; and so on....
gt;
gt; Many thanks
gt;
gt; Location Department
gt;
gt; Head OfficeProduction
gt; BirminghamSales
gt; LondonFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeFinance
gt; Head OfficeSales
gt; Head OfficeSales
gt; Head OfficeSales
gt; Head OfficeFinance
gt; Head OfficeSales
gt; Head OfficeFinance
gt; LondonProduction
gt; LondonProduction
gt; LondonProduction
gt; LondonProduction
gt; LondonProduction
gt; LondonSales
gt; LondonSales
gt; LondonSales
gt; LondonSales
gt; LondonFinance
gt; LondonFinance
gt; BirminghamProduction
gt; BirminghamProduction
gt; BirminghamProduction
gt; BirminghamProduction
gt;

Wow, quick response!

Thanks everyone, works a treat!

quot;Gary''s Studentquot; wrote:

gt; One option is to use a pivot table. Drag Location and Department into the
gt; Row area and Count of Department into the data area and what you should see
gt; is:
gt;
gt;
gt; Count of Department
gt; LocationDepartmentTotal
gt; BirminghamProduction4
gt; Sales1
gt; Birmingham Total5
gt; Head OfficeFinance9
gt; Production1
gt; Sales4
gt; Head Office Total14
gt; LondonFinance3
gt; Production5
gt; Sales4
gt; London Total12
gt; Grand Total31
gt;
gt; --
gt; Gary''s Student
gt;
gt;
gt; quot;Nickquot; wrote:
gt;
gt; gt; Hi I have a list of data shown below, in 2 cloumns.
gt; gt;
gt; gt; I want to do a count if to show the total people in each department and
gt; gt; location i.e.
gt; gt; COUNT the number of occurances of people in
gt; gt;
gt; gt; HEAD OFFICE and PRODUCTION
gt; gt;
gt; gt; and then
gt; gt;
gt; gt; the number of occurances of people in
gt; gt;
gt; gt; HEAD OFFICE and FINANCE
gt; gt;
gt; gt; and so on....
gt; gt;
gt; gt; Many thanks
gt; gt;
gt; gt; Location Department
gt; gt;
gt; gt; Head OfficeProduction
gt; gt; BirminghamSales
gt; gt; LondonFinance
gt; gt; Head OfficeFinance
gt; gt; Head OfficeFinance
gt; gt; Head OfficeFinance
gt; gt; Head OfficeFinance
gt; gt; Head OfficeFinance
gt; gt; Head OfficeFinance
gt; gt; Head OfficeFinance
gt; gt; Head OfficeSales
gt; gt; Head OfficeSales
gt; gt; Head OfficeSales
gt; gt; Head OfficeFinance
gt; gt; Head OfficeSales
gt; gt; Head OfficeFinance
gt; gt; LondonProduction
gt; gt; LondonProduction
gt; gt; LondonProduction
gt; gt; LondonProduction
gt; gt; LondonProduction
gt; gt; LondonSales
gt; gt; LondonSales
gt; gt; LondonSales
gt; gt; LondonSales
gt; gt; LondonFinance
gt; gt; LondonFinance
gt; gt; BirminghamProduction
gt; gt; BirminghamProduction
gt; gt; BirminghamProduction
gt; gt; BirminghamProduction
gt; gt;