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

0
Nick4562 (282)
1/12/2006 4:19:06 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
308 Views

Similar Articles

[PageSpeed] 39

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" <Nick@discussions.microsoft.com> wrote in message 
news:4672C41C-2852-48A0-B76A-87058BD4E35E@microsoft.com...
> 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
> 


0
bliengme5824 (3040)
1/12/2006 4:27:41 PM
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)

0
CycleZen (674)
1/12/2006 4:34:15 PM
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
> 
0
pinmaster (74)
1/12/2006 4:39:04 PM
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
> 
0
GarysStudent (1572)
1/12/2006 4:40:05 PM
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
> > 
0
Nick4562 (282)
1/12/2006 5:06:05 PM
Reply:

Similar Artilces:

Linking 2 workbooks
What is the formula/procedure to link an entry in a Source workbook to an identified name in another workbook? Example: Sam makes numerous donations over time that are entered into the main spreadsheet. Entry of a given donation should automatically appear as a separate entry in his own identified workbook. ...

2 different Deleted Items folders??
Decided to try Outlook after years with OE. Set up email and news accounts. Imported messages from OE. When I open Outlook, show Folders, I see Outlook Today - [Personal Folders], Deleted Items, Inbox, etc. I emptied the Deleted Items folder. Then, View, Goto, News, show Folders, I see Outlook Newsreader, Local Folders, then most of the same folders that I see under Personal Folders in the Outlook window, including another Deleted Items folder, and this one isn't empty. Where did I go wrong? Al The Outlook Newsreader is actually Outlook Express which maintains its own folder...

MS Money small business for Canada #2
Hi, I would like to purchase MS Money small business to handle my company accountings. I am located in Canada and was wondering how flexible this product is to be customized for Canadian Tax system and payroll rules. Regards, Alex In microsoft.public.money, Alex wrote: > > >I would like to purchase MS Money small business to handle my company >accountings. I am located in Canada and was wondering how flexible this >product is to be customized for Canadian Tax system and payroll rules. The last such product was Money 2000. Today only Standard is available for Canada. Th...

get number of monday, tuesday,... between 2 dates
Hi, how can I get number of monday, tuesday,... between 2 dates? Thanks, Racsus (Racsus@discussions.microsoft.com) writes: > Hi, how can I get number of monday, tuesday,... between 2 dates? DECLARE @offset int, @day varchar(10) SELECT @day = 'Tuesday' SELECT @offset = CASE @day WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Mittwoch' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 ...

MSPOS 2.0
To change the behavior of the screen's min/max buttons one must enable it through the POS View. Need to be able to set this in Manager View also for non-POS machines without having to hack the database. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.m...

Auto Format #2
Can't find Auto Format, did excel 2007 decide not to have this function, is there a similiar function to be used instead of autoformat? Hi Ness! If you want to select a formatting for your table select Format as Table in the Home Tab of the Ribbon. I believe that is the replacement for the Auto Format command in the previous versions of Microsoft Excel. Hope this Helps! -- Chester C. Coronel Junior BSIT Student University of Asia and the Pacific, Philippines Read the StudentEmpowered Blog!http://msforums.ph/blogs/chestercoronel "Ness" wrote: > Can't find Auto F...

Count Results
I have a table with a list of suggestions in it, which users can add to. When I open the main screen, I have a subform in there linked to the table for the suggestions and have a text box in the form. When a user adds a new suggestion, it checks a field called "activesuggestion", which i have as a yes/no box in the table. In the text box in the form, I have used the =count to try and return the number of new records added since last viewing the database =Count([activesuggestion]) This returns all records in the table, but I would like to limit it to those which ...

HAVING / Where time count / excluding any instance
Greetings this almost New Year's Eve, I have a set of data with open and close dates (for one person). I want to calculate the duration the person was open. But I can't do [close] - [open] because some of the closures are errors--you can identify an error if the next open date is the next month. For example, 5/16/2000 closures is an error--because reopened 6/27/2000. So really, I want to subtract 1/1/2000 date from final closure of that period--which is 4/25/02 Open Close 2/15/1990 4/20/1992 8/1/1995 11/12/1995 1/1/2000 5/16/2000 6/27/2000 12/7/2001 1/4/2002 4/...

Unable to send emails #2
My mail server is not sending out emails. When I perform an NSLookup on the server's IP address I get an UNKNOWN for the server name. Any ideas would be appreciated. Thanks "TPSchaefer" <TPSchaefer@hotmail.com> wrote: >My mail server is not sending out emails. When I perform an NSLookup on the >server's IP address I get an UNKNOWN for the server name. > >Any ideas would be appreciated. Fix your DNS or your network connection to your DNS. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm I have...

XML ASP.NET 2.o Issue
Hi, I have the following code and I'm using News.xsl to read the XML file - Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load '---------------------------------------------------------------------------- THIS PAST (myXml.Document) IS NOT WORKING IN ASP.NET 2.0 '---------------------------------------------------------------------------- myXml.Document = getXML(nSource.SelectedItem.Value) End Sub Function getXML(ByVal sourceFile As String) Dim myRequest As System.Net.WebRequest = System.Net.WebRequest....

ms pos 1.2 weird word jumble
Store Operations Manager was not working, so I did a repair install. After the repair, the wrong phrases were in the wrong spot. For example, instead of cancel it says "category code" and instead of file it says "Filter:" Does anyone has an idea how to fix this? I took a screenshot of the home screen so you can see the weirdness for yourself: http://picasaweb.google.com/MissVoldemort/Weird#5349426894521446370 ------------------------------------- ##-----------------------------------------------## Newsgroup Access Courtesy http://www.rockryno.com/ Tax and Account...

How to create a rule which triggers when a condition is NOT met?
.... for instance, in OLExpress and Pegasus one can create a rule with the symbol "@" in the conditions and request the rule to "move to the Junk email folder" all mail which do NOT meet the condition. I can't seem to find this facility anywhere in MS Outlook. Thanks for your help in advance and apologies if this question has already been asked and answered elsewhere (if it *has* I would be grateful for the link). Regards, russel You can achieve this by creating a rule which works on all mail (by not having any selection criteria) then use "except" th...

Problem with Conditional format deletion
I have the followint formula in a conditional format. =ROW()=ROW(INDIRECT(CELL("address"))) I remember getting it from this group, it highlights the active cell in yellow. I cannot delete it and I believe it will not allow me to copy and paste data about the spreadsheet. I need to delete as I need to copy and paste more than I need the active cell highlighted. Hi if you want to highlight always the active cell you need VBA. See: http://www.cpearson.com/excel/excelM.htm#HighlightActiveCell -- Regards Frank Kabel Frankfurt, Germany <mark@southwestconst.com> schrieb im Newsbei...

Xml validation against xsd #2
I have a xml documnet to be validated against an xsd. The xsd is specified in the doc as <books xmlns="my-bookshelf" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:SchemaLocation="my-bookshelf books.xsd"> <book publisher="QUE"> <title>XML By Example</title> <author>Benoit Marchal</author> <price>20.0</price> </book> </books> the xsd is: <?xml version="1.0" encoding="UTF-8" ?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema&...

Missing Recipients #2
We are running Exchange Server 2003 on Windows Server 2003 with all of the service packs for both. Recently we noticed that when users sent email sometimes the recipients would appar blank. The recipients were present when the email was sent and they also received the message, however when we look in sent items or the recipient pulls up the message in their inbox it will just display a blank field in the To field. I am certain that they are not using the BCC field and it happens to multiple users so it's not specific to somebody's computer. What is causing this issue? ...

Multiplication error #2
I noticed that Excel 2003 calculates: 7.5*1.2=8.7 when the correct answer is 9.0. It did this as part of a formula where the 1.2 answer came from calculating another set of numbers. When I asked Excel to calculate this answer by simply inputting those two numbers the correct answer of 9.0 came out. Does anyone have an answer as to why it does that?! Hmmm.... 8.7. Well, 7.5+1.2=8.7. Are you sure there's not a plus sign in there? ******************* ~Anne Troy www.OfficeArticles.com "DeeJay" <DeeJay@discussions.microsoft.com> wrote in message news:72EA8922-F5BA-486...

Auto Assigning Categories #2
Hello does any one know if this is possible: Can I bulk assign categories to transactions that have already been imported into money from my bank. Is there a way to assign one transaction then have Money update all similar transactions with my the new category assignment. Also will money remember the new category assignment and auto assign all similar future transactions? -- Joe In microsoft.public.money, Joe Robison wrote: >Hello does any one know if this is possible: Can I bulk assign categories to >transactions that have already been imported into money from my bank. I...

dynamic named range #2
thank-you, as usual, top notch! E -- ExcelMonke ----------------------------------------------------------------------- ExcelMonkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=522 View this thread: http://www.excelforum.com/showthread.php?threadid=26678 ...

Conditional formatting dates 05-05-10
I have a range of dates that I want to conditionally format. Eg, todays date is the 5 May 2010. I want to highlight all of the dates in the range D14 to D120 that todays date passes on a daily basis. For eg, as we click over automatically to the 6 May 2010, any fixed dates in the range for the 5 May 2010 turn red. Any help would be great. Select your dates and use Custom in CF and the formula =D4=TODAY()-1 -- Regards Dave Hawley www.ozgrid.com "Sue" <Sue@discussions.microsoft.com> wrote in message news:E87D631B-E455-4698-92A1-BE915192ECDF@microsoft.co...

sorting text in 2 columns by likeness
I have 2 columns of text with number values associated with them. Amy 21 Amanda 12 Bill 12 Amy 18 Chris 32 Bill 23 Kathy 8 Chris 12 Kim 15 Curtis 32 Todd 23 Katrhy 21 Todd 5 I need to be able to sort these like this Amanda 12 Amy 21 Amy 18 Bill 12 Bill 23 Chris 32 Chris ...

heading #2
Hi, How to use a certain row to be as a header on each page when printing? thanks Assuming pre-XL2007: File | page Setup | open the Sheet Tab | specify which rows (and/or columns) are to be printed on each page. You CANNOT do this from PrintPreview best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Khalil handal" <khhandal@yahoo.com> wrote in message news:OqHuYqJQIHA.5976@TK2MSFTNGP05.phx.gbl... > Hi, > How to use a certain row to be as a header on each page when printing? > thanks > > ...

Conditional Formatting with Formulae
I'm quite happy getting part of a row to format as I wish by using a formula and applying it to a range but I want to use this about 50 times in a single spreadsheet and I cant stop the range being absolute so when I drag/fill I don't get the effect I want. Is there way to fill down conditional formats which have been generated using a formula? -- Rod Not with FrontPage. Try asking in a newsgroup or forum that deals with spreadsheets. -- Ron Symonds Microsoft MVP (Expression Web) http://www.rxs-enterprises.org/fp/wf-menu.aspx RODXL presented the following e...

Printing an A5 booklet 2 up on A4 paper
Sorry for what is a long story but hopefully, "what I am trying to do and why I cant do it", will be clear enough by the time you get to the end. This really is a simple requirement that was easy to do and worked fine in every version of Publisher prior to 2007! I am printing a booklet that is basically, an A5 page folded in half containing 28 pages. The printer it is sent to is a HP8550 with duplex. The page setup is set correctly as Booklet, 10.5cm x 14.85cm prints 4 pages per piece of A5 paper and it prints it in the correct order, paginated etc. The problem started when...

Exchange move #2
Hi Everyone Are there any issues with moving an Exchange Server 2003 install on a Windows 2000 server to a Windows 2003 server Thanks in advance Greg Depends on how you move it, I guess. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Greg Edmonds" <g.edmonds@uq.edu.au> wrote in message news:515CF446-48EE-4F26-9141-65FE6E461711@microsoft.com... > Hi Everyone, > > Are there any issues with moving an Exchange Server 2003 install on a Windows 2000 server to a Windows 2003 server? > > Thanks in advance, > > Greg...

Display names #2
planning a migration from exchange 5.5 to a live 2003 organisation, both in seperate organistations. The 2003 uses a display name of 'surname, firstname' and the 5.5 uses 'firstname, surname', wanted to know the easiest way of bulk changing the 5.5 display names to 'surname, firstname'? -- Many Thanks Nikrep <Nikrep@discussions.microsoft.com> wrote: >planning a migration from exchange 5.5 to a live 2003 organisation, both in >seperate organistations. The 2003 uses a display name of 'surname, >firstname' and the 5.5 uses 'firstname...