How can I count dates if few duplicates in a column

I entered few dates in Column C3 to C20, few of them are duplicates.
How can I count total number of dates (excluding duplicates), duplicates 
should be count 1.
0
Utf
5/25/2010 3:17:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1096 Views

Similar Articles

[PageSpeed] 2

Try the below

=SUMPRODUCT(--(C3:C20<>""),1/COUNTIF(C3:C20,C3:C20&""))

-- 
Jacob (MVP - Excel)


"Tariq Aziz" wrote:

> I entered few dates in Column C3 to C20, few of them are duplicates.
> How can I count total number of dates (excluding duplicates), duplicates 
> should be count 1.
0
Utf
5/25/2010 3:26:03 PM
Hi,

Try this

=SUMPRODUCT((C3:C20<>"")/COUNTIF(C3:C20,C3:C20&""))
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Tariq Aziz" wrote:

> I entered few dates in Column C3 to C20, few of them are duplicates.
> How can I count total number of dates (excluding duplicates), duplicates 
> should be count 1.
0
Utf
5/25/2010 3:28:02 PM
As long as they are true Excel dates...

=SUM(--(FREQUENCY(C3:C20,C3:C20)>0))

-- 
Biff
Microsoft Excel MVP


"Tariq Aziz" <Tariq Aziz@discussions.microsoft.com> wrote in message 
news:2D5CD894-1809-4DB7-A86D-9EBDBAF4B9FC@microsoft.com...
>I entered few dates in Column C3 to C20, few of them are duplicates.
> How can I count total number of dates (excluding duplicates), duplicates
> should be count 1. 


0
T
5/25/2010 4:07:21 PM
Reply:

Similar Artilces:

Can't find where to change view!
I have Outlook 2002 all fully patched both on my Desktop and on my Laptop. When I view a particular message on my desktop version, it views in HTML. When I view the SAME message in Outlook on the laptop, it's just plain text. I can't for the life of me, find the option in Outlook on the laptop to change that! (It's probably SO obvious I just haven't seen it!) Thanks -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk Do you have the Slovak Attachment Options add-in installed on the laptop? If so, check to see if the "Re...

negative values causing column title problem
How do I get the Column titles x axis lables at he bottom of the chart field when have negative values It s friday and this is killing me. Select the axis Choose Format>Selected axis On the Patterns tab, for Tick Mark Labels, select Low TFrisch wrote: > How do I get the Column titles x axis lables at he bottom > of the chart field when have negative values > > It s friday and this is killing me. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html hey thanks - boy was thaqt annoying >-----Original Message----- >Select th...

Unsolvable cell naming question: Can anyone beat this one?
I am trying to copy and paste named cells from one workbook to another without having the first workbook file name attach to the named cells. For example, I am copying a cell named XYZ from file One.xls to file Two.xls. I need to have cell XYZ NOT say One.xls!XYZ when it is entered into Two.xls. Also, how can I get that message that asks if I want to use the cell names to not pop up? I think I understand what you're running into here. If the cell you are MOVING refers to a named range in it's source workbook that doesn't exist in the destination workbook, Excel will not ide...

counting data from a series
I have from a suvey in a 3 column table, where COL1=survey number, COL2=question number, and COL3=response, as such: COL1 COL2 COL3 a Q1 2 b Q1 3 c Q1 1 a Q2 3 b Q2 3 c Q2 1 a Q3 2 b Q3 3 c Q3 1 etc.. How can I calculate all the "3" responses for all Q2s? For example, based on the above sample table, I would expect the formula to return "2". =SUMPRODUCT(--(B1:B9="Q2"),--(C1:C9=3)) Hope this helps! In article ...

Help: Money Transfer Transaction Dates
I was using Money 2001 and recently upgraded to Money 2004. One the issues i've had with both Money and prior versions of Quicken is the inability to accurately record the dates for money transfers from one account to another (especially from a Checking account to a Credit Card) where the posting dates are different. For example... If i transfer x dollars from my checking account to pay off my MasterCard... my bank statement shows the transaction occuring on 01 OCT 2003 whereas my credit card statement shows the transaction occuring on 30 SEP 2003. When entering the transfer / payment...

Problem: new calendar entries land on wrong date
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange When making a new calendar entry, it will not land on the date I select, but rather on: March 20, 2020!!!! <br><br>The only way in which I can make a calendar entry is to make it in iCal, and then let iCal synch with Entourage calendar. <br><br>Likewise, the Entourage calendar, when printing, will only show/print imported holidays, not the actual entries as displayed. <br><br>What's wrong? <br> I have re-installed Office from scratch - no imp...

Can I insert a box on excel that can be checked and un-checked?
I would like to insert a box that can be checked or unchecked, like a check list showing what is done and what is not done. I was just wondering where I can find that. Thanks Right click on your toolbar, on the drop down pick Forms, should be there Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=494386 ...

Add a goal line to a column chart
I have several column charts which I need to add different goal lines. Any suggestions? The goal has to be represented as a line and not as a column. Thanks for your help! http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brandy" <Brandy @discussions.microsoft.com> wrote in message news:EF99E151-13A4-461D-B9CA-DECE4E467904@microsoft.com... >I have several column charts which I need to add different goal lines. Any > sugg...

Error can't empty clipboard appears when trying to copy.
When I try to copy and paste a table from Excel into Word I am getting the error "can't empty clipboard" appears after hitting the copy function...This has happened a couple of times but only after extended use of the program. When I pull up the clipboard there is nothing on it at all...I opened the clipboard on every program I have running to see if its full somewhere else but it isn't. ...

can not download explorer 8
When I go to download explorer 8 and go to run it this comes up and wont let me download the procedure entry point SHRegGetValueW could not be located in the dynamic link library SHLWAPI.dll. How can I be able to download explorer 8? There is a very good chance that you are seeing the effects of a hijackware infection! NB: If you had no anti-virus application installed or the subscription had expired *when the machine first got infected* and/or your subscription has since expired and/or the machine's not been kept fully-patched at Windows Update, don't waste your...

lookup date, sum up total...
Hello all, Here is my situation: Worksheet 1 A B C D 1.)1/1/05 Data Data P&L 2.)1/2/05 Data Data P&L .. .. .. 365.) 12/31/05 Data Data P&L ............................................................ Worksheet 2 A B 1.) January =IF(Worksheet1=January, then sum all january P&L (Ignore other months)) 2.) February =IF(Worksheet1=February, then sum all February P&L (Ignore other months)) .. .. .. 12.) December =IF(Worksheet1=December, then sum all DecemberP&L (Ignore other months)) what formula do I need to...

Unhiding Protected Rows and Columns? is it possible?
I have a workbook with a number of worksheets that all interlink. I have protected a number of cells in one of the sheets. the problem arrises in that the unprotected cells cannot be hidden or indeed unhidden when the sheet is protected. Is there a way I can protect cells whilst still keeping the formatting ability to widen / hide rows etc. thanks -- guilf ------------------------------------------------------------------------ guilf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25608 View this thread: http://www.excelforum.com/showthread.php?threadid=39...

Gridlines on Column Chart
Hello- I am trying to create a stacked column chart with gridlines that only appear for every 2 columns. How can I do this in Excel 2007? All I can find is how to insert a major gridline between every column. Thanks, Sean Hi, Format the X axis, on the Axis Options section set 'Interval between tickmarks' to 2. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "SeanF74" <SeanF74@discussions.microsoft.com> wrote in message news:E1BE2939-AB52-4FB1-8A0F-ACA9EFDB4CAD@microsoft.com... > Hello- > > I am trying to create a stacked ...

RE: Can't connect to OWA
Lee Derbyshire [MVP] wrote: > 404 means 'page not found'. Have a look in IIS Manager on the OWA > server, and make sure that it still has a Virtual Directory named > 'Exchange'. > > Lee. > > Yes, IIS has still a Virtual Directory named "Exchange". When I try to connect via IE to the OWA from the OWA Server I see the logon form, but after having provided any username and password (or domain\username and password) I receive the HTTP 404 error. ...

date subtraction difficulty
I'm trying to subtract dates, to calculate time duration. I'm subtracting column A from column B into column C. Column C is formatted 'm "mos", dd "days"' and columns A and B are formatted 'mm/dd/yy/@' Unfortunately each of the durations is 1 month too long. That is, if the real duration is 4 days, column C will display '1 mos, 4 days' Any clue what I'm doing here? Thanks much. On Tue, 18 Jul 2006 11:58:10 -0500, Peter <facetious_nickname@hotmail.com> wrote: >I'm trying to subtract dates, to calculate time durati...

How can I copy address book from MSN to MS Outlook?
...

OWA
I have full permissions to other users' mailboxes, and can access them in Outlook, but when I try in OWA I receive the error "You are not authorized to view this page." The way I try to access the another user's mailbox is by appending /[email alias]/inbox. (I've also tried multiple combinations of substituting email alias with user logon name and keeping/dropping "inbox.") This worked at one time, but I haven't tried it in a while. So either some updates I have applied to my server (sbs 2003 premium - running Exhange 2003) or some other configuration ch...

How can I enumerate directory content in MFC?
Hello, I have a program that has to auto save some data automatically. I want to list autosave directory content and find the file with biggest serial number e.g. AutoSave_1, AutoSave_2, AutoSave_3,... and then create a file that its name is one greater than biggest value. I have to list directory content and then look for names. Problem is I couldn't find any info on MSDN on how I cando this in MFC. the only enumeration method I could find was under .Net How should I do this in MFC? thanks a lot. >Hello, I have a program that has to auto save some data automatically. I want >...

Variable Column Lookup
I have three columns to the left column K each has a header with the year number. 2008, 2009 and 2010. Underneath each of the three columns I have numerical data. On the same header row I have a drop down list in K1. So when I pick 2009, I want the calculations in K2, K3 downward to use the corresponding data in column that has the matching year. 2008 2009 2010 2009 (drop down) 2 3 6 3*10 If I choose the drop down of 2008 then the formula should be 2*10 If I choose the drop down of 2010 then the formula should be 6*10 The actual formula is much more complic...

Stacked Column Charting
Has anyone tried the following? From the data set below, I would like to have the data to the right of the Hrs. avail data be a stacked bar for each day. Along with this the Hrs. avail data would be a bar next to the remaing row of data. I tried placing the Hrs. avail on its own axis but that did not seem to work. In short I want to show the number of hours availabe in a day and actual hours used by the verious other data. Regards, Hal Day Hrs. avail EM's PM's Cal's Projects Other Idle 1 14 4 2.5 2 4 0 1.5 2 14 5 1 1 3 0 4 3 14 0 2 2 6 0 4 4 7 2 0 0 3 0 2 5 14 2 0 0 10 0...

excel 2004 column sorting
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I've read up a bit on others' sorting issues, but haven't come across exactly the same problem as mine. I used to be able to sort the info in one column and the corresponding info in the other columns would sort along with whichever column I chose at the first. Now, my corresponding columns don't sort along with my first sort, resulting in inaccurate info. Also, the workbook I am working with contains macros; the workbook contained macros when I first began using using it, i.e. when sorting was no prob....

insert number in column #2
I have a column with threedigits for telephone extensions. I want to insert a digit before the three throughout the column as the extension has been increased to 4 digits. Any easy way.../ Thanks. AFD One way: In a blank column (say column H), enter H1: =1 & A1 or, if you want them as numbers rather than text: H1: =1000+A1 copy down as far as necessary. Select the column and copy it. Select the original column and choose Edit/Paste Special/Values. In article <Om6vrJpEIHA.3548@TK2MSFTNGP06.phx.gbl>, "afdmello" <afdmello@hotmail.com> wrote: &...

Help with counting user entered text values
Hi, Version: Excel 2000 Situation: In Column G, I will have testers entering in their initals. I would like to display the tester's initals in column H of another worksheet with the number of times that set of intals appeared in column G on column I of the other worksheet. The catch is that I do not have a list of the testers initals ahead of time, so I'll need to pull them from column H. Thanks for any and all help in advance, David Hi David, H10: DAL i10: =COUNTIF(G:G,H10) or if you don't want to see 0 if H10 is empty I10: =IF(H10="","",COU...

Why can't I see shading on my screen?
I cannot see pale shading on my screen. This happened with watermarks--I inserted one, but could not tell if it worked and had to go look at it on another comptuer. And I opened a document with some paragraphs shaded in pale green and couldn't see them. I'm using Word 2003 on Windows XP. Thanks, Suroor If the same shading in the same document shows up on another computer, then you need to adjust the contrast setting of your monitor. There should be buttons or dials on the bottom of the monitor to make the adjustments. -- Regards, Jay Freedman Microsoft Word MVP ...

Changing to new dates
Hi everyone I am trying to change last year of date to new year i.e from 2003 to 2004.I have done them day by day and if i start changing date i have to do for 365 days .Can any one tell me how can i change those years e.g 01/01/0 02/01/03 this goes up to 31/12/0 and i want it to be changed to 2004 year plz help me im going crazy!!! Just put the first date in the first cell assume it is A1, in A2 put =A1+1 then just copy down the formula -- Regards, Peo Sjoblom "Ali" <anonymous@discussions.microsoft.com> wrote in message news:8B773478-C8A1-4F88-AE9D-CD3DD5B8878...