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
732 Views

Similar Articles

[PageSpeed] 46

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:

Printing Multiple Ranges
I have a worksheet that has 16 different ranges (entitled Page1, Page2, ..., Page16). I want to print each page in its correct order. So I go to Page Setup and make the print area be "Page1, Page2, Page3" and so on. But instead of keeping the ranges in that order, it converts them to cells so the print_area becomes J139:ER193. This wouldn't be a problem, except that the pages aren't in the correct order in the worksheet, so when I print I get page 6, 7, 8, 13, 1.... So I ask, is there a simple way to do this in excel? I also tried to write a macro to do this, a...

Advanced Search Folder Criteria
Outlook 2007 I want to create a search folder that contains compound criteria (search for x AND y). In the Search Folder Criteria dialog box, on the Advanced tab, there is a "More Advanced..." button, but it is grayed out. How do I enable that button, and is there a way to create a search folder with compound criteria? --Tom Hello Tom, You need to enable the query builder, see this microsoft Knowledgebase article http://support.microsoft.com/?kbid=3D307922 On Dec 17 2008, 3:52=A0pm, "Thomas M." <NoEmailRepl...@Please.com> wrote: > Outlook 2007 > &g...

count the number of new lows within a range of cells
Hello, Is there a formula or method to count the number of new lows within a range of cells? For example, the following are in col A rows 5 to 14. Within this set a new low has been reached 3 times- (96,95,94). What I would like is a formula or macro that that would produce the results - 3. There are about 9000 rows of data and I am looking for "count of new lows within the preceding 10 rows" on a rolling basis. Thanks. 100 101 96 97 98 97 95 97 94 95 Version 1: Use this if the 9000 or so rows don't have blanks in them. In this version you just need to select the first cel...

count string in cell
Hi All In my excel one of column is Status in detail worksheet This column have multi status. e.g. Color Error,Size Error,Data Matched. I want check how many error by type. In other sheet call Statistics, check how many status in detail worksheet. e.g. Color Error Size Error Data Matched I am using =COUNTIF('Item List'!T:T,A4) where T is Status Column. Funciton Countif just handle one value in status. Do you know which function can handle multi status ? If you want one cell to count all 3 of those criteria... One way... =SUMPRODUCT(--(ISNUMBER(MATCH(&...

Multiple sent messages
I am having trouble with Outlook XP 2003 sending out many copies of e-mails to the people I have addressed the e- mail to. It fills their boxes and keeps sending. I have run a virus scan several times and can't find a virus. This only seems to happen when I have a rather large attachment. I have seen other posts that people are having a similar problem. Does anyone know how to fix this problem?? ...

Count Records
Hi Guys, How can I count records in a a combobox to allow me to scroll using the slider bar without first having to scroll to the bottom of the list? Regards John Count the records in the rowsource of the combobox: NumRecord = DCount("*","NameOfTheRowSource") PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "John" <johnlee2509@btopenworld.com> wrote in message news:0E1DBE7E-AD54-4715-A547-EC2DDE9CDE39@microsoft.com... > Hi Guys, > How can I count records in a a combobox...

Consolidation if a least one criteria is met
Dear all, I have the follwoing Name Value Criteria Aby 7 #N/A Oby 9 #N/A Ubi 1 #N/A Orbi 9 Yes Aby 6 Yes Oby 2 Yes and I would like to consolidate the data if "yes" is present at least once for the name e.g. Aby = 13, Oby = 11, Orbi = 9. I tried this formula in column D =SUMIFS(B2:B6,A2:A6,A2,C2:C6,OR(C2:C6="Yes",C2:C6)) and remove the duplicates but the formula does not add value with criteria = "Yes" any idea? Thank you HI Look at this: =IF(COUNTIFS($C$2:$C$7,"Y...

Unread count is always (1) when there are no unread messages
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop I have tried everything - marking as unread, selecting all and marking as unread followed by marking all as read, cmd-alt-t, cmd-l. It still comes up with one unread message which I cannot find. <br><br>How can I fix this? On 1/22/10 12:30 AM, in article 59bb16b0.-1@webcrossing.JaKIaxP2ac0, "Lou_C@officeformac.com" <Lou_C@officeformac.com> wrote: > have tried everything - marking as unread, selecting all and marking as > unread followed by marking all as r...

Sum cells using criteria from a row and a column
I am trying to sum cells in a sheet based on matching criteria in a row and matching criteria in a column. The data that I am working with is represented as: Resource: Resource1 JAN JAN JAN JAN FEB Project Project Description 12/19 12/26 1/2 1/9 1/16 Admin Administration 10 10 10 10 10 Holiday Holiday 8 8 Other Other time Off Training Training 20 Vacation Vacation 22 30 10 30 22 I ...

a simple count question ... ?
I'm using Excel 2007 and in column A I have a list of items that I wish to have a total count of. I have spaces (empty cells) between some items in column A to make it easier to group (and to see) but was wondering if there's a way to count the cells that only contain info. In case I haven't described this clearly, I don't want an addition of these items (they're words and not numbers), I just want to know how many cells in column A contain data. Thanks, Dave Horne Hi Dave =COUNTA(A1:A100) Regards, Per "Dave Horne" <davehorne@home.nl> skrev i m...

Working with multiple email accounts
This is a multi-part message in MIME format. ------=_NextPart_000_003C_01C5BB08.821A6EB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable So, I have three email addresses configured on Outlook- one personal, = and two business (one personal business, and one generic). I have a few = questions. =20 First question: On my email at work (not related to the aforementioned business emails), = I have two inbox's - one for me, and I also have access to my boss's = email. But, on Outlook at home, I can only have one inbox, and I had to =...

Multiple monitors
I was having issues trying to do updates and the solution I found was to check off basevideo option in boot.ini on the msconfig panel. Well you can imagine this would be a pain to do being you have to reboot everytime you do this plus it disables my other 2 monitors. Anyone know a fix for this? I have searched MS knowledge base and this was the only fix I found. You might be better off asking this in one of the Windows groups.... -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for ...

How to color multiple cells
I have a sheet for a soccer tournament with 10 teams that have to play games each home and away. , this means that each team appears 18 time in this sheet. How can I color each team (cell) without having to colo one cell at a time. I will use different colors for each team. Thanks a million for your help in advance. P/S, ex: Column A Column B USA vs. France France vs. USA USA vs. Italy Italy Vs. USA USA vs. Spain Spain vs. US Attachment filename: premier division.xls Download attachment: http://www.excelforum.com...

Multiple Domains in Exchange 2000
I have a client collecting email from 4 different domains using SBS2K/Exchange2K The problem is that one of the departments gets some of its mail through an ISP mailbox. Exchange recipient policy has to have this ISP domain listed and checked or email coming in with this address is rejected. Yet because this is a popular ISP there is a requirement to be able to send outgoing email to OTHER subscribers using this same ISP.... But Exchange sees itself as solely responsible for the ISP domain and won't pass outgoing messages addressed to it. Does anybody have any ideas how to get round th...

Generating Multiple Choice Tests
I currently use Access 2003 to create tests. I am looking to use a grouping and random sampling method to improve efficiency. In as much that I use access to create tests now, is VBA an appropriate platform for randomizing and group sampling or should I go to an independent language. Any advice will be greatly appreciated. -- mhm karst, ******UNTESTED... Something like... SELECT TOP 1 Rnd([TestID]) AS Expr1, TestText FROM tblTests ORDER BY Rnd([TestID]) -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, inform...

count passed or failed
im having a problem getting the correct formula for this one. i used =IF((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000),"Passed","Failed") and =COUNTIF(DATA!$L$2:$L$5000, "name@email.com") and it gave me the desired output. but the problem is, i have to get the total number of passed and failed for each person in my team. i can't seem to find the exact formula to combine those conditions. please help me, thanks! Neri, =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)= (DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) *(DATA!$L$2:$L$...

select multiple items from dropdown
hello everyone, i have a table which is populated using a form. one of the fields is person's name, the dropdown for which picks up data from a second table (which has an index and person's name). what i want to do is modify the form so that a user can select multiple items from the dropdown, but i havent been able fo find a way to do so. i am using access 2003. i know that access 2007 lets you do that, but i dont have an option of upgrading right now. i would appreciate your suggestions thanks, vc-programmer Create a related table, where you can store many options for one...

Print Column Headings on multiple sheets
I have a budget that when printed is one page wide and three tall. How can I add the column headings to the second and third pages? click file/page setup and click the sheet tab click the icon next to rows to repeat at top select the rows click the icon again and then ok -- Gary Keramidas Excel 2003 "wx4usa" <wx4usa@gmail.com> wrote in message news:19421c6b-0ea5-4524-8574-8dea96d44bde@22g2000yqr.googlegroups.com... >I have a budget that when printed is one page wide and three tall. How > can I add the column headings to the second and third pages?...

Creating drop down lists with multiple options?
I am trying in vain to create a drop down list where users can select more than 1 choice. For example, if the choices in a list which i named are as follows: Blue Red Green Yellow Purple How does one have the option to select any of the colors by themselves or any combination of all of them up? For example, if the question is what is your favorite color? How can a user choose Blue OR Red and Blue OR Red, Blue and Green and so on? Any help would be appreciated. That will require multiple cells. One cell will only hold 1 value. Any single cell solutions are going ...

How can I sync multiple calendars separately in outlook?
I have Outlook 2007 and I am having an issue syncing calenders. I am currently using an exchange server from work that syncs my default calendar with the work calendar. I also use Google calendar for my personal information and use this to sync with my iPhone. I was using the Google sync program to sync my google calender into outlook as well. Is there a way to make my google calendar and my work Exchange calendar sync to 2 different calendars in Outlook so that they do not automatically combine? That would be a feature of the sync tool and the last time I used google's ...

Multiple Commas 02-11-10
Some people decided to play a cruel joke and pulled two commas into a name field and i need to split the name into a first and last name column. Normally i could do this but the second comma is throwing me off. It looks like this: last name,first name, Ive tried doing a replace on the last comma but its not working. Does anyone know any tricks on how to either get rid of that second comma or split the field into two seperate columns as it is? Thanks for any help. dim aName() as string dim strName as string dim strLastName as string dim strFirstName as string ' just ...

Customer Statement - Footer summary
-------------------------------------------------------------------------- 7/1/2005 11:34:00 AM CDT -- Stephanie Drilling Can you asssit with a report modification issue? (8248456) We are working on the User Defined Statement format. We have national accounts Cash is applied at the parent account level We wanted a statement that provides a detail of the items by child account with subtotal, then in the report footer, a summary of child account with balance. We can get everything but the footer to do what we want. I have enclosed screen prints and the package file of the report. Any i...

Printing Copies or multiple worksheets produces mult jobs
When I print multiple copies of a worksheet or I print an entire workbook that contains multiple worksheets, Excel breaks the print job up into multiple print jobs of (apparently) random size. This creates a problem for instance, when printing 2 sided because the printer "resets" after the first print job and the subsequent print jobs do not carry the requested attributes (2 sided, in this case). Has anyone experienced this problem. Any fixes? Thanks. BGW wrote: > When I print multiple copies of a worksheet or I print an > entire workbook that contains multiple wo...

Way to put multiple rows into the same column.
I have data that is in the format of 3 columns wide by about 3000 rows. I want to rearrange each row to become a single column. Eg. Cells A1,B1,C1,A2,B2,C2 to become in cells A1,A2,A3,A4,A5,A6 respectively. I have transposed the data but this only puts each row in it's own column. I still need to put all the new columns into one column. Thankyou for any help given. Hi, Try my EastyText_Rev1.xls at http://www.geocities.com/excelmarksway It might work, er, maybe, hmmm... - Mark >-----Original Message----- >I have data that is in the format of 3 columns wide by about 3000 r...

Multiple Column Conditional Formatting
Hi, I'm needing some help with performing a multiple column condition formatting. I have tried many "forumula is" in the conditional formatting with no success. I'm missing something. What I would like to have happen is, if the 2 conditions are true then I want the field to be color filled. Example: if the numeric value in k2>10 and the numeric value F2>100 then I would like cell K2 to be filled in red. Many thanks for your help Colleen one way: Select K2: CF1: Formula is =AND(K2>10,F2>100) Format1: <patterns>/Red In article &...