Count a value excluding duplicates depending on a set criteria

Hi, hope someone can help,

I have data that has multiple entrys for a date, but I only want to count 
each day as one using a formula in excel 2007.

	A	B	C	D
1	Dept	Crew	Type	Date
2	301	A	1	1/02/2009
3	302	C 	2	1/02/2009
4	301	A	1	1/02/2009
5	301	A	1	2/02/2009
6	302	C 	2	1/02/2009
7	303	D	2	1/02/2009
8	301	B	1	9/02/2009
9	301	A	2	1/02/2009
10	303	D	2	9/02/2009

E.g. I want to know how many days a crew worked in the above, 'Dept' = 
"301", 'Crew' = "A", 'Type' = "1", how many days.  Would equal 2 days.


0
Utf
2/2/2010 6:33:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1709 Views

Similar Articles

[PageSpeed] 9

One way...

Array entered** :

=COUNT(1/FREQUENCY(IF(A2:A10=301,IF(B2:B10="A",IF(C2:C10=1,D2:D10))),D2:D10))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"Jum" <Jum@discussions.microsoft.com> wrote in message 
news:B6895EE6-1BF2-4457-A9CF-869005AE1380@microsoft.com...
> Hi, hope someone can help,
>
> I have data that has multiple entrys for a date, but I only want to count
> each day as one using a formula in excel 2007.
>
> A B C D
> 1 Dept Crew Type Date
> 2 301 A 1 1/02/2009
> 3 302 C 2 1/02/2009
> 4 301 A 1 1/02/2009
> 5 301 A 1 2/02/2009
> 6 302 C 2 1/02/2009
> 7 303 D 2 1/02/2009
> 8 301 B 1 9/02/2009
> 9 301 A 2 1/02/2009
> 10 303 D 2 9/02/2009
>
> E.g. I want to know how many days a crew worked in the above, 'Dept' =
> "301", 'Crew' = "A", 'Type' = "1", how many days.  Would equal 2 days.
>
> 


0
T
2/2/2010 7:00:37 AM
Reply:

Similar Artilces:

Summing rows based on criteria in another column
I'm trying to consolidate rows of data based on the ID and OP column. In the attached image, I would like to consolidate ID # 190 because OP column is blank or zero and leave the other rows of data as is if there is a value in the OP column. +-------------------------------------------------------------------+ |Filename: excel problem.gif | |Download: http://www.excelforum.com/attachment.php?postid=4732 | +-------------------------------------------------------------------+ -- reybie -----------------------------------------------------------...

Migrating Outlook settings from local user account to domain user accounts
Howdy - we are in the process of moving all of our workstations onto a WS2k3 controlled domain. Most of our users have Outlook 2003 as the mail client, and I'm trying to find a way to move all their settings across onto the new profile of their machines (we use an IMAP server, not an exchange server btw) At the moment we are using a script to copy most of their old settings (desktop, quick launch, my docs etc) from their local profile into the new profile location on the server - however we're not getting the outlook settings. Whilst all the settings can be recreated reasonably easi...

Counting Commissions in a Report
Hello all. I have a database that tracks Commissions for recruiters (Access 2003/2003). I need to come up with a commission report on a monthly basis, which is not a problem. Where I'm having the logic problem is.... Sub-Contractor XYZ works on Project 123. He works at site ABC from 10/1/07 - 10/1/07. He works at site DEF from 10/3/07 - 10/3/07. He works at site GHI from 10/6/07 - 10/6/07. He works at site JKL from 10/9/07 - 10/9/07. etc... I need to generate a report that shows for the first site, the commission would be $5.00. The second site and onward would be $2.50. I would lik...

How can you Copy an email and set up to send hourly?
Okay, here is my question: Can you set up outlook 2002 SP3 to copy an exact email (to same person, same subject and same message) to someone... also setting up a begin date to end date and send it our hourly? Here is why I ask, I found an online contest/Sweepstakes that excepts hourly entries via email. I have been working with "new email" (opening multiple times), setting it not to send until a certian date and hour (for each day and hour of the day) and using the edit then clipboard to add in all the fields (to, subject and message) to match the criteria as stated in the Contest/S...

How do I automatically change color of value if value is > 50 in .
Hi, I want to change the color of the value when it reaches a value that is > a certain value. Use 'Conditional Formatting' on the 'Format' menu Jock W "Vichingo" wrote: > Hi, > > I want to change the color of the value when it reaches a value that is > a > certain value. conditional formatting is what you want. Menu Format>Conditional Formatting, should be straight-forward what to do. -- HTH RP (remove nothere from the email address if mailing direct) "Vichingo" <Vichingo@discussions.microsoft.com> wrote in message ...

Cell Value + open application
Hi all, the following is what I am trying to accomplish: I have a spread sheet that I use to track incoming and outgoing documents. What I would like to be able to do is have cell in which I enter a name into open up and outlook template I have saved in the same directory (.oft file). Also be able to a pull information from other cells to populate the subject of the template. Thanks in advance for any assistance with this ...

Can no longer set new appointments in Outlook 2003
I've been using Outlook 2003 for some years without any problems. Today, I can no longer create new appointments, or view existing ones. I get a message in the reading pane stating 'This message with restricted permission cannot be viewed inthe reading pane until you verify your credentials. Open the item to read it's contents and verify your credentials.' "Desmac" <Desmac@discussions.microsoft.com> wrote in message news:EC212132-7061-456E-AB9A-02F960E311A4@microsoft.com... > I've been using Outlook 2003 for some years without any problems....

Links not showing updated value
I have linked spreadsheets and I have noticed that one of the links is not showing an updated figure. How can I 'refresh' the spreadheet so that it shows the new value? Wanson try Edit/Links and click Update Values any use? Nick -- Nicko ------------------------------------------------------------------------ Nicko's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29477 View this thread: http://www.excelforum.com/showthread.php?threadid=491779 Thanks for this Nick. I did Edit/links, open souce/update now and then saved both the source and ...

want to set properties in C#
If I have an xml file and want to set some properties in it (read and write it I guess), what is the best alternative? I don't want to use xlst - this seems like I should be able to just specify the node and replace the parameter similar to what I am doing now in InstallScript: oConfig = CreateObject("MSXML.DOMDocument"); oConfig.load(configFile); oLanguageCtl = oConfig.selectSingleNode("Config/GeneralDlgs/LocaleInfoDlg/LanguageCtl"); oLanguageCtl.text = Locale; .... oConfig.save(configFile); for the parameter shown here: <Config xmlns:xsi="http://www.w3.org...

Default value in a lookup field
Hello, Could someone help me : How can we set a default value in a lookup field ? Thanks regards WT If in 1.2 use the Form editor, select the field, click the Properties option and then the values Tab. Should be easy from there. If 3.0 then you need to go to Settings, Customization, Customise Entities, Double click the relevant entity name, Click Attributes, Double click the fieldname and you should see what you need. Be aware that MS have not allowed all picklists to be edited. If you can't edit them they are greyed out. -- Simon Morris "WT" wrote: > Hello, &...

Database function criteria
I am using DGET and DSUM in a data table to return data from a long list. All works fine, except that DGET returns NUM! errors for some fields because it is trying to return 2 values rather than one unique value. This is because it seems not to distinguish between my criteria. For example, it thinks that line items 'Depreciation' and 'DEPRECIATIONS' are the same. The Excel help mentions that the functions are not case sensitive, but even correcting for the case of the criteria, I have some that DGET thinks are the same. Does anyone know the exact behaviour of the databas...

Problem setting up Outlook 2003 newsgroup
I've found a bunch of messages with the same problem, but no solution so far. I recently installed Outlook 2003. I followed the directions for adding th news icon to the GO menu and it appeared to work. I clicked on the NEWS icon and it started to take me through first time setup. However, I cancelled part way throught in order to find out my DNNS setting. When I tried to return, clicking on NEWS opened a folder, rather than the first time setup program Thinking that I should start from scratch again, I deleted the NEWS icon from the GO menu. But when I went back to add the icon ag...

cell reads as label but returns a value
How do you have a cell read on the spread as a lable (using the label in a drop-down list) and when selected, the cell will return a constant value? The cell might read: "Red Widget" but it would return a value of say: 257. Got any ideas on this one.... Thanks... Spydor -- spydor ------------------------------------------------------------------------ spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28438 View this thread: http://www.excelforum.com/showthread.php?threadid=480383 Hi spydor, Is this what you mean? http://hanjohn.customer....

Chart Series from single value
I have a series of charts (Bar-Line) embedded on a worksheet. The bars in each chart represent monthly values and are dynamically set using VBA code, this works well. The line needs to extend across the chart for the number of months in the chart, the value of this line is the same at each point and is stored as a single value in one cell on the worksheet. Is this possible? -- Regards, Nigel nigelnospam@9sw.co.uk I usually advise people to use as many cells as makes what they want easy. You can use this approach: http://peltiertech.com/Excel/Charts/AddLineHorzErrBar.html to add...

How to set focus or select next cell
I have five fields of inputs (say B5,C5,D5,E5,F5), after putting the data in cell F5, when I hit the enter key is there a way that the next focus cell is B6 for the next input, if so how? In other words after data is inputted in cell F5 the focus jumps to the next line at cell B6 ... thanks ahead for the solution. -dave Hi Dave Select the range B5:F62 before you start typing and it will behave like that for quite a while. HTH. Best wishes Harald "dave wagner" <dwagner@lancorpnet.com> skrev i melding news:IHbaf.7447$D13.133@newssvr11.news.prodigy.com... > I have...

Formula for transposing Row Values to Col Values
Excel2003 ... WS1 ... Row Range L2:Z2 ... Contains Text WS2 ... Col Range B3:B17 ... I wish formula to populate Text from WS1 ==> I know I can do this with Copy/Paste/Special/Transpose ... However, I wish a Formula to do it as I need to apply in additional places ... Thanks ... Kha One way... With Values in A1, B1, C1... use this formula in A3 and drag down =OFFSET($A$1, 0, ROW() - 3, 1, 1) Note that this function is volatile and as such will add a lot of calculation overhead to your spreadsheet. If you use a lot of these your performance will suffer... -- H...

Where I can find Value Added Chain Diagram in Visio?
Or is this diagram in different name in Visio? I can't find it... If you mean Value Stream Map, there is a new template for this diagram in Visio 2007 Professional. -- Mark Nelson Office Graphics - Visio Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Dia" <Dia@discussions.microsoft.com> wrote in message news:9E5C79D9-54B7-4115-9709-85AB28169CA1@microsoft.com... > Or is this diagram in different name in Visio? I can't find it... ...

Blackberry/ Outlook Calendar Duplication Problem
We've been having this wierd Outlook/ Blackberry problem. I am configured for aireless Calendar Sync. Someone sends an appointment request, I accept on Backberry, and the appointment shows up in the Calendar both places; then I accept on Outlook, it dupes... THEN i go to Deleted Items on Outlook, re- accept, the dupe disappears! Strange. Anyone seen this? I have similar problem "SamAntix" wrote: > We've been having this wierd Outlook/ Blackberry problem. I am configured for > aireless Calendar Sync. Someone sends an appointment request, I accept on > B...

default input language in wince 6.0 regional settings is not enabl
Hi All, Can anybody know how to enable the default input language in regional settings for the targeted device using PB and catalog items. The original Wince BSP with existing target board is working fine but after cloning the BSP for custom board and adding the catalog items it's not showing the default input language was enabled. ...

Setting UOM
Hi, We defined our product as inventory product and services. The UOM was either Each or Mandays. My client intends to have let say he picks inventory product on the opportunity product form. The UOM can be automatically inserted after he chooses the product. Can it be accommodated on CRM 3.0? How is the workaround? Carrie ...

how do I find duplicate text enteries in Excel
What I am trying to do is use a list of products in my spread sheets. When an item is duplicated under another heading then I want to highlight it Use conditional formatting. Look at 'Highlight cells that meet specific conditions' in the Answer wizard in help -- HTH Bob Phillips "Cambronze" <Cambronze@discussions.microsoft.com> wrote in message news:72C9CE6A-1D19-434C-A3D6-12231C08566D@microsoft.com... > What I am trying to do is use a list of products in my spread sheets. When > an item is duplicated under another heading then I want to highlight it ...

Set Default Inbox When Outlook Starts
How do I set a default Inbox that Outlook will open when the program is launched? Right now it opens an "Inbox" that is unused; I want it to point to my IMAP mailbox. Thanks in advance. Freddy <none@none.com> wrote: > How do I set a default Inbox that Outlook will open when the program > is launched? Right now it opens an "Inbox" that is unused; I want it > to point to my IMAP mailbox. Tools>Options>Other>Advanced Options. Set your startup folder there. -- Brian Tillman ...

Converting lead to only contacts and opportunity (gives wrong value)
When we convert lead to ONLY (contact and opportunity ), then we dont get correct value in "potential customer" filed at opportunity . Value of first name and last name comes in "potential customer" lookup field (from lead). BUT:: when we convert the lead to account , opportunity nd contact , everything goes well. anyone can test and let me know whats wrong !! /Aami I know parent customer is the unique identifier for account and contact . But still if anyone has to say anything or i rest my case :=). if anyone has any interest : the only solution i had in mind...

Text values to numeric values
Is there a formula to convert a text value in a cell to a numeri value? Thanks, Jayveeja -- jayveeja ----------------------------------------------------------------------- jayveejay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=504 View this thread: http://www.excelforum.com/showthread.php?threadid=39463 Good afternoon Jatveejay Yes there is. Say your numeric value is in A1, in B1 the formula: =VALUE(A1) will show the number as a value. If you wanted to convert a number in its cell from a text number to a value number, ie., not use a formula in a di...

counting cells #4
Hi this may seem a daft question but i'm having real problems trying to count cells... i want to count cells in a range say a17 to a40 inclusive - i want to know the total number of cells in a range and for it to include all types eg empty cells,text numbers and formulas sam Hi try: =ROWS(range)*COLUMNS(range) -- Regards Frank Kabel Frankfurt, Germany "dave h" <usenet@nospamsoremovesupersports.plus.com> schrieb im Newsbeitrag news:OfxV20OoEHA.1800@TK2MSFTNGP15.phx.gbl... > Hi > > this may seem a daft question but i'm having real problems trying to c...