Distinct Counts with Multiple Criteria

Each month we produce a complex report in Excel using thousands of rows of 
data.  We've just been asked to add in a new table which needs to use 
distinct counts based on multiple criteria.  I've included an example of the 
data below.  How can I create a formula to complete the results table as I 
have manually entered below?				
				
	A	B	C	D
1	NAME	DEPT	LOCATION	TYPE
2	Mary	Finance	A	Primary
3	Sally	Finance	B	Primary
4	Sally	Finance	B	Primary
5	Mary	IT	A	Primary
6	John	Finance	B	Secondary
7	John	Finance	B	Secondary
8	David	Admin	A	Primary
9	John	IT	B	Secondary
10	David	Finance	A	Primary
11	David	IT	C	Primary
12				
13				
14	Results Table for Locations A & B			
15	DEPT	Primary	Secondary	
16	Finance	3	1	
17	Admin	1	0	
18	IT	1	1	

Thanks 
BT
0
Utf
5/27/2010 9:05:56 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
627 Views

Similar Articles

[PageSpeed] 14

Please note that this is an array formula.You create array formulas in the 
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
enter the formula. If successful in 'Formula Bar' you can notice the curly 
braces at both ends like "{=<formula>}"

=SUM(IF(FREQUENCY(IF((A2:A11<>"")*(B2:B11="Finance")*
(D2:D11="Primary"),MATCH(A2:A11,A2:A11,0)),
ROW(A2:A11)-ROW(A2)+1),1))

-- 
Jacob (MVP - Excel)


"Bentam3" wrote:

> Each month we produce a complex report in Excel using thousands of rows of 
> data.  We've just been asked to add in a new table which needs to use 
> distinct counts based on multiple criteria.  I've included an example of the 
> data below.  How can I create a formula to complete the results table as I 
> have manually entered below?				
> 				
> 	A	B	C	D
> 1	NAME	DEPT	LOCATION	TYPE
> 2	Mary	Finance	A	Primary
> 3	Sally	Finance	B	Primary
> 4	Sally	Finance	B	Primary
> 5	Mary	IT	A	Primary
> 6	John	Finance	B	Secondary
> 7	John	Finance	B	Secondary
> 8	David	Admin	A	Primary
> 9	John	IT	B	Secondary
> 10	David	Finance	A	Primary
> 11	David	IT	C	Primary
> 12				
> 13				
> 14	Results Table for Locations A & B			
> 15	DEPT	Primary	Secondary	
> 16	Finance	3	1	
> 17	Admin	1	0	
> 18	IT	1	1	
> 
> Thanks 
> BT
0
Utf
5/27/2010 9:35:27 PM
Reply:

Similar Artilces:

Faster way of linking multiple cells between spreadsheets?
I have a spreadsheet for each on-going projects and one summary spreadsheet. Each time I create a spreadsheet for a new project, I have to go back and forth between the new spreadsheet and my summary spreadsheet and go thru the "=" and "Enter" process for each cell i want to link to. All the cells I want to link to are in one block (row) and I tried to do the "= and Enter" thing for just the first cell and highlighting the whole block i wish to link to but it only does a straight copy... no link is created so when i update the cells later on... my summary is not...

Multiple functions
I am attempting to use a combination of "IF" and "IS" statments to solve for multiple scenarios. For example, I need the following to be solved with the IF statement: 1) If a price is changed but the item number stays the same, then give me the supplier that is in cell AJ7 2) If no change is made, then give me the supplier that is in cell C7 3) If a new item number has been entered to replace a deleted item number, show me the supplier that is in cell C7 4) Delete item with no replacement I have tried the following to solve and I can only get 2 out of ...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

record count of table while looping through all tables
Hi, I am using the following code to loop through all the tables in my db. While looping through the tables, I would like to get the record count of each table. At the ****, I'm using rs.RecordCount, but that is just returning the count of the tables, not the count of the record in each table. Do I need to do something like 'rs(rs!Name).record count'? -Ben ------ Public Sub AppendTableRecords() Dim strSelectAllTables As String strSelectAllTables = "SELECT NAME FROM MSysObjects WHERE Type=1 AND Name not like 'MSys%';" Dim rs As Ne...

Multiple Palms and Outlooks
I have a Palm 505 and my wife has a Palm Pilot. We would like to both like to use our home Outlook to manage personal calendar information. BUt we do not want to sync my work things (from my work Outlook) and some of her work things. Is there anyway to selectively choose the things to be synced? We are using Outlook 2002 at home, Outlook 2000 at work and Chapura Desk Mirror. THANKS! If you do not want your work items to appear, simply do not synch your work calendars. Also, if you are using profiles at home, you should not have a problem synching there either. --� Milly Staples ...

Mail merge of 2 worksheets, using 2nd sheet for criteria to merge
I am trying to mail merge an Excel file containing 2 worksheets into a letter created with Word. Sheet1 contains all contact information that will be merged into the header of the letter. Sheet2 contains the criteria that will select which rows to merge. For instance, "Date Received" is a column header in Sheet2. We want to merge rows that have 9/10/2003 in the column "Date Received." Right now we are using a workaround by putting an extra column in sheet1 and manually typing an "X" in the rows we want to merge. (then selecting Query Options before running th...

Counting working days.
A1=2/13/2009 10:00 A2=2/14/2009 10:00 A3=A2-A1 I get an answer of "1 day 00:00" which means 1 day 0 hours 00 minutes. This is on a 24 hour a day basis. How can I get an answer on an 8 hour a day basis?? What would be the correct answer for your example data? It is subject to interpretation. At the simplest, the formula would be =(B1-A1)*24/8 =3 which indicate that there are 3 8-hour period between the datetime values, but somehow I don't think that is what you are looking for. Provide more detail. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Pro...

CRM Integration 1.2 for Great Plains
The CRM Integration 1.2 for Great Plains seems to only support integration between one Great Plains company databse and an installation of CRM. Does anyone know if it is possible to integrate more than one Great Plains database to a CRM installation using the the standard CRM Integration 1.2 for Great Plains and Biztalk server. CRM <=> GP Integration only supports integraiton to one GP company DB. >-----Original Message----- >The CRM Integration 1.2 for Great Plains seems to only support >integration between one Great Plains company databse and an >installation of CRM....

Query structure and distinct
Hi I have a query where I am trying to return one "hearing" record (child record) per "case" record (parent record) if the three WHERE qualifiers are met. When use a DISTINCT statement however, the query omits hearing records when there are multiple hearing records and only 1 record meets the qualifier statements. When I don't use DISTINCT, the query obviously includes multiple hearing records for a case record where more than 1 record meets the qualifiers--which we don't want. I am including the query below. If anyone has any thoughts I would defi...

OWA with multiple IP Addresses
Our exchange server 2003 handles multiple domains, each domain has its own unique ip assigned for handling e-mails and all go to the same exchange 2003 server. How can I configure exchange 2003 owa to respond to any one of the ip and not just the primary when brining up owa. Right now owa login page is available only if the primary ip address is used. ex: http://www.ip1.com/exchange will bring up the owa login page and http://www.ip2.com/exchange or http://www.ip3.com/exchange will display page not found. Could you not just use one IP address but setup multiple virtual directories...

How can I have multiple "If" statements in one formula?
I would like to write a formula that looks at one cell and sees if there is a certain letter, R, L or C. and depending on what letter is there it would add so many days to another date. I am sure I did it before but I do not remember how I did it. Can anyone help? =if(a1="R",5,if(a1="L",7,if(a1="C",10,"input error"))) If you've got more than three or four such conditions, you'll be better served by using a vlookup. "MikeL" wrote: > I would like to write a formula that looks at one cell and sees if there is a > certain le...

a Counting question
hi to all I'm sure this is easy, but I'm new to Excel. I need a function that will COUNT all the numbers in a range (B5:B1525) equal to or greater than the value in A3. (all values are positive integers) thanks in advance -- cinnie =SUMIF(B5:B1525,">" & A3) -- Jacob "cinnie" wrote: > hi to all > > I'm sure this is easy, but I'm new to Excel. > I need a function that will COUNT all the numbers in a range (B5:B1525) > equal to or greater than the value in A3. (all values are positive integers) > &g...

Equation Evaluation at Multiple Points
I have seen this done but how to do it eludes me. What I'm looking for goes something like this. Fill a column with numbers (like 1, 2, 3,...) Fill the next column with the same equation. Somehow "associate" column 1 and 2 so that the equation is evaluated for the value in the first column. Select all of column 2 cells Enter formula needed Press CTRL+ENTER all cells filled with formula. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email <stevelreid@aol.com> wrote in message news:1135779938.108941.255910@f14g2000cwb.googlegroups.com... >I hav...

How do I count the words in Publisher newsletter article?
I would like to know how to count the words in a Publisher newsletter article. The template always lists the suggested number of words--is there a counter in the program? Copy the text, paste into Word. Word can perform word counts. -- Mary Sauer http://msauer.mvps.org/ "mj" <mj@discussions.microsoft.com> wrote in message news:B7EBD7F8-B193-4B85-B50D-FCA2DD23E4DA@microsoft.com... >I would like to know how to count the words in a Publisher newsletter > article. The template always lists the suggested number of words--is there a > counter in the program? ...

Using IIF statements with counts
I am trying to have different paragraphs displayed in a report based on whether or not a claimant has more than one claim. I am using a subreport to pull in the claims for the entity. How would I set up the syntax to do the following? Claim number field = [Claim] Claim amount = [Amount] Entity = [Company] If claimant has one claim... =[Company] & "Per our record, your claim " & [Claim] & "has the amount of " & [Amount] & " please confirm this matches..." But if the claimant has more than one claim, I need to use a differe...

multiple selection
How can I select multiple records into a list box? I want that for append identical records (for select records) in another table. Many thanks Florin ���"florin" <florin_serban@hotmail.com>�� ��� ������ news:eus6IBxcIHA.748@TK2MSFTNGP04.phx.gbl... > How can I select multiple records into a list box? > I want that for append identical records (for select records) > in another table. > Many thanks > Florin >��� �� ����� ��� ��� Any ideea? "florin" <florin_serban@hotmail.com> wrote in message news:eus6IBxcIHA.748@TK2MSFTNGP04.phx.gbl......

How do you print multiple journal entries.
I want to use Outlook to record meetings with contacts. I would like to use journal entries to record the meetings, but I need to be able to print them out all at once, to individually. ...

Counting Distinct Values
I have three columns of data. Here's a subset: ZIP Cope Name County 42312 John Franklin 42342 John Franklin 43545 John Franklin 42343 Mary Franklin 43786 Mary Franklin 48988 Dave Fairfield 47676 Dave Fairfield 47623 Mary Fairfiled 43589 Joe Fairfield 45646 Mike Fairfeld I need a formula to count the number of people per cou...

Mortgage debt counted twice in budget
I am using MS Money 2004 and its counting the mortgage twice in my monthly budget. I have checked the bill planner and when I setup the budget and it was only being counted once at that point. How do I get rid of the double counting issue. I have read some of the earlier posts, but they don't make sense to me. HELP!! I have nothing setup in the debt reduction planner. Thx ...

multiple events in single .vcs file
How do I create a file that has mutliple events in one single file? I would like to create a file that has all of the sporting events listed for employees to be reminded in their calendar. http://www.calendar-updates.com You can create your own Holidays/Events calendar using the instructions on this page: http://www.slipstick.com/calendar/holidays.htm#company --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After search...

Grouping
I'm trying to create a query from 2 joined tables. EMP & Quizzes are joined on EMP_ID In the results, I'd like to have one row for each employee with a column showing their store, ID, name, and hire date (from the EMP table) and then a column for each quiz date and quiz score for each quiz number. The Quizzes table has a field for quiz number (b01 - b05), date taken, and score. It should come out something like this: STORE EMP_ID NAME Hire Date b01 Date b01 Score b02 Date b02 Score b03 Date b03 Score b04 Date b04 Score b05 Date b05 Score 3 84 JOE TEST 1/16/2009 12/2...

referencing multiple worksheets in macro
So I kind of found the answer to my question in a post from a couple years ago but am still confused so if anyone could help I'd appreciate it...I'm trying to protect the sheet while still being able to use the subtotal function. I have several worksheets I want to do this for and this is the post from before with the code but I can't figure out where to put the names of my worksheets ("01","02","03") and what I need to customize in the code for my file. Any suggestions? Option Explicit Sub auto_open() dim wks as worksheet ...

Problems with multiple accounts?
Posting here since I got no response in microsoft.public.outlook.general: Does Outlook 2000 have a problem with multiple email accounts? I am having a problem with it at home and at the office, on Windows 2000 Pro and Windows XP Pro. When I close it on 2000, I almost always have to go into Task Manager and manually end outlook.exe as a process. Once or twice a week, I have to do the same at the office. Sometimes when I start Outlook, it takes a couple of minutes to do so. There are no plug-ins or PDAs for either installation. The only common trait I see is that I have about accounts in either...

distinct records
Hi Folks, I am trying to query one table for distinct records based on only 2 fields. I want all the columns returned, but I only want to use 2 columns to be referenced for unqiueness. I could reference the whole record for distinctness but the timestamp is of the clock's time is still distinct, BUT they are the same user. So I want to reference their name and phone number only yet still return all the rows and have the results put into a new table. Here is my idea for the SQL: SELECT * INTO Table1 FROM Table WHERE (basically this is where I would want to say where the contact name ...

Questions about COUNT ??
I have a column of figures that has both postivie and negative numbers. -Is there a function that will tell me how many positive numbers there are and how many negative numbers there are? -Is there a function that will actually add only the positive numbers as a sum and only the negative numbers as a sum? thanks to all for the help . . =COUNTIF(A:A,">0") =COUNTIF(A:A,"<0") =SUMIF(A:A,">0") =SUMIF(A:A,"<0") -- HTH Bob Phillips "rs" <r_schiller@REMOVEcomcast.net> wrote in message news:Q6adnYvuzeb3enfcRVn-pg@...