Count ifs - 2 conditions

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 Office Production
Birmingham Sales
London Finance
Head Office Finance
Head Office Finance
Head Office Finance
Head Office Finance
Head Office Finance
Head Office Finance
Head Office Finance
Head Office Sales
Head Office Sales
Head Office Sales
Head Office Finance
Head Office Sales
Head Office Finance
London Production
London Production
London Production
London Production
London Production
London Sales
London Sales
London Sales
London Sales
London Finance
London Finance
Birmingham Production
Birmingham Production
Birmingham Production
Birmingham Production

A Pivot Table would work nicely here, or use SUMPRODUCT
Let your data be in A1:B100
In D2:D5 (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

"Nick" <[email protected]> wrote in message
news:[email protected]...
> 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 Office Production
> Birmingham Sales
> London Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Sales
> Head Office Sales
> Head Office Sales
> Head Office Finance
> Head Office Sales
> Head Office Finance
> London Production
> London Production
> London Production
> London Production
> London Production
> London Sales
> London Sales
> London Sales
> London Sales
> London Finance
> London Finance
> Birmingham Production
> Birmingham Production
> Birmingham Production
> Birmingham Production
>

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
Birmingham Finance
Birmingham Production
Birmingham Sales

Head Office Finance
Head Office Production
Head Office Sales

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="head office")*(B1:B10="production"))

HTH
JG

"Nick" wrote:

> 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 Office Production
> Birmingham Sales
> London Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Sales
> Head Office Sales
> Head Office Sales
> Head Office Finance
> Head Office Sales
> Head Office Finance
> London Production
> London Production
> London Production
> London Production
> London Production
> London Sales
> London Sales
> London Sales
> London Sales
> London Finance
> London Finance
> Birmingham Production
> Birmingham Production
> Birmingham Production
> Birmingham Production
>

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
Location Department Total
Birmingham Production 4
Sales 1
Birmingham Total 5
Head Office Finance 9
Production 1
Sales 4
Head Office Total 14
London Finance 3
Production 5
Sales 4
London Total 12
Grand Total 31

--
Gary''s Student


"Nick" wrote:

> 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 Office Production
> Birmingham Sales
> London Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Finance
> Head Office Sales
> Head Office Sales
> Head Office Sales
> Head Office Finance
> Head Office Sales
> Head Office Finance
> London Production
> London Production
> London Production
> London Production
> London Production
> London Sales
> London Sales
> London Sales
> London Sales
> London Finance
> London Finance
> Birmingham Production
> Birmingham Production
> Birmingham Production
> Birmingham Production
>

Wow, quick response!

Thanks everyone, works a treat!

"Gary''s Student" wrote:

> 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
> Location Department Total
> Birmingham Production 4
> Sales 1
> Birmingham Total 5
> Head Office Finance 9
> Production 1
> Sales 4
> Head Office Total 14
> London Finance 3
> Production 5
> Sales 4
> London Total 12
> Grand Total 31
>
> --
> Gary''s Student
>
>
> "Nick" wrote:
>
> > 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 Office Production
> > Birmingham Sales
> > London Finance
> > Head Office Finance
> > Head Office Finance
> > Head Office Finance
> > Head Office Finance
> > Head Office Finance
> > Head Office Finance
> > Head Office Finance
> > Head Office Sales
> > Head Office Sales
> > Head Office Sales
> > Head Office Finance
> > Head Office Sales
> > Head Office Finance
> > London Production
> > London Production
> > London Production
> > London Production
> > London Production
> > London Sales
> > London Sales
> > London Sales
> > London Sales
> > London Finance
> > London Finance
> > Birmingham Production
> > Birmingham Production
> > Birmingham Production
> > Birmingham Production
> >