Counting individual dates

I have a column of dates, some dates are repeated, and I would like a count 
of the number of separate dates. 

My sample column:
1/1/05
1/1/05
1/2/05

I want my return to be 2, because I have 2 different dates.

Is there a way to do this?

Thanx!

0
robin (55)
6/14/2005 10:57:07 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
293 Views

Similar Articles

[PageSpeed] 13

Try...

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Hope this helps!

In article <3FD3EE87-AFC2-4854-9E70-1E1155D5798F@microsoft.com>,
 "Robin" <Robin@discussions.microsoft.com> wrote:

> I have a column of dates, some dates are repeated, and I would like a count 
> of the number of separate dates. 
> 
> My sample column:
> 1/1/05
> 1/1/05
> 1/2/05
> 
> I want my return to be 2, because I have 2 different dates.
> 
> Is there a way to do this?
> 
> Thanx!
0
domenic22 (716)
6/14/2005 11:13:16 PM
THANX Domenic... that did the trick!  Now, where can I go to understand that 
formula?  

"Domenic" wrote:

> Try...
> 
> =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
> 
> Hope this helps!
> 
> In article <3FD3EE87-AFC2-4854-9E70-1E1155D5798F@microsoft.com>,
>  "Robin" <Robin@discussions.microsoft.com> wrote:
> 
> > I have a column of dates, some dates are repeated, and I would like a count 
> > of the number of separate dates. 
> > 
> > My sample column:
> > 1/1/05
> > 1/1/05
> > 1/2/05
> > 
> > I want my return to be 2, because I have 2 different dates.
> > 
> > Is there a way to do this?
> > 
> > Thanx!
> 
0
robin (55)
6/15/2005 12:01:07 AM
Assuming...

A1:  1/1/05
A2:  1/1/05
A3:
A4:  1/2/05

Formula...

=SUMPRODUCT((A1:A4<>"")/COUNTIF(A1:A4,A1:A4&""))

Here's how it breaks down...

(A1:A4<>"") returns the following array of values:

TRUE
TRUE
FALSE
TRUE

COUNTIF(A1:A4,A1:A4&"") returns the following array of numbers:

2
2
1
1

(A1:A4<>"")/COUNTIF(A1:A4,A1:A4&"") returns the following array of 
numbers:

0.5
0.5
0
1

Note that the numerical equivalent of TRUE and FALSE is 1 and 0, 
respectively. So the first array of values are divided by the second 
array of values...

TRUE/2 -----> 0.5
TRUE/2 -----> 0.5
FALSE/1 ---->  0
TRUE/1 ----->  1

Then, lastly, SUMPRODUCT sums these values and returns 2.

Hope this helps!

In article <7004565A-C6A2-4CF2-881B-54D16C9430A9@microsoft.com>,
 "Robin" <Robin@discussions.microsoft.com> wrote:

> THANX Domenic... that did the trick!  Now, where can I go to understand that 
> formula?  
> 
> "Domenic" wrote:
> 
> > Try...
> > 
> > =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
> > 
> > Hope this helps!
> > 
> > In article <3FD3EE87-AFC2-4854-9E70-1E1155D5798F@microsoft.com>,
> >  "Robin" <Robin@discussions.microsoft.com> wrote:
> > 
> > > I have a column of dates, some dates are repeated, and I would like a 
> > > count 
> > > of the number of separate dates. 
> > > 
> > > My sample column:
> > > 1/1/05
> > > 1/1/05
> > > 1/2/05
> > > 
> > > I want my return to be 2, because I have 2 different dates.
> > > 
> > > Is there a way to do this?
> > > 
> > > Thanx!
> >
0
domenic22 (716)
6/15/2005 3:04:14 AM
Excellent breakdown explanation....

Never did understood the SUMPRODUCT() either... now, I guess I can use
this too!!


-- 
terabar
------------------------------------------------------------------------
terabar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24272
View this thread: http://www.excelforum.com/showthread.php?threadid=379192

0
6/15/2005 5:58:55 AM
This will give you a detailed explanation of the function and it's uses:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"terabar" <terabar.1qnlyb_1118815517.5594@excelforum-nospam.com> wrote in
message news:terabar.1qnlyb_1118815517.5594@excelforum-nospam.com...
>
> Excellent breakdown explanation....
>
> Never did understood the SUMPRODUCT() either... now, I guess I can use
> this too!!
>
>
> -- 
> terabar
> ------------------------------------------------------------------------
> terabar's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24272
> View this thread: http://www.excelforum.com/showthread.php?threadid=379192
>

0
ragdyer1 (4060)
6/15/2005 6:02:13 PM
Reply:

Similar Artilces:

count the number of rows with entries greater than zero
i need a formula that will look at the information in a range of column within a row. if the number in a column is greater than zero, then i need to add one to the total. this will allow me to know the number of columns with values greater than zero. =COUNTIF(A2:Z2,">0") -- Best Regards, Luke M "r2rcode" <r2rcode@discussions.microsoft.com> wrote in message news:3C5B05F0-39E5-49F7-B93C-45F70851AD50@microsoft.com... >i need a formula that will look at the information in a range of column > within a row. if the number in a column is great...

dates appear as error hashes
When I type dates in cells on an Excel spreadsheet they become a series of hashes. But other dates of the same format in other workbooks are fine. Why does this error keep on occuring. I think may be the cells are not in the right format for me to add dates. Can i change this?? Hi Try widening the columns. Does this resolve the problem? Regards Roger Govier pembers wrote: > When I type dates in cells on an Excel spreadsheet they become a series of > hashes. But other dates of the same format in other workbooks are fine. Why > does this error keep on occuring. I think may be...

Help with date ranges
Hi all, I've got a sheet where I have some columns with dates in and I want a column with the number of working days between them. I've used the DAYS360 function, which gives a rough indication of the number of days, but it's not very accurate. Can anyone give me an idea of now to do it, a script, a plug in, or a different app I can download to do it? Help really appreciated. -- MattG Look at NETWORKDAYS and WORKDAY in help, they are part of office/excel but need to be installed (Analysis ToolPak) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "MattG" ...

how to get rid of default dates in Excel?
I want my dates to stay as I have typed them - not for them to be changed for the Excel default system. Then you must format the cells to your date entry style. Either before or after. BTW............Excel does not control default date format. That is controlled by your Windows Regional and Language settings. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 07:51:01 -0700, Margaret L. B. <Margaret L. B.@discussions.microsoft.com> wrote: >I want my dates to stay as I have typed them - not for them to be changed >for the Excel default system. 2 optio...

Using Date as a Variable
I have cell contents and formulae in cells with fixed dates in them. I need to create other versions of the workbook with different dates. So I created a date variable. But when I run the procedure to replace the fixed dates with another date, I get an error message: Run-time error '1004': Application-defined or object-defined error Here is a simple example of what I have in the code. Dim sCripDate As String sCripDate =3D "2007,10,10" With Worksheets("Cost") .Range("D8").Value =3D "=3DDATE(sCripDate)" End With I must be...

count records based on date range
I have a list of records about which I'm trying to summarize differen information. One of the columns in the spreadsheet is a created date. What I want to do is create three summary fields, one that shows a coun of the records from today to six days ago, a second field that shows count of all records from seven to 14 days ago and the last fiel showing a count of all records over 15 days ago. This sheet will b updated regularly, so I don't want to have specific dates in m formulas. I used the COUNTIF function to do summarization for different part of the spreadsheet, but I can'...

Date Question
In Money 2006, In previous versions of Money you could set the check register to show either the date an EPay was processed by the back or the date it was due at the Vendor. In Money 2006 I can't seem to find that setting. Can anyone direct me to the right place? Thanks. Steve ...

How to Change Date Formats
I have a bunch of raw data that I am working with. One of the columns displays dates and I am trying to strip out the year. If I try to use the YEAR function I get strange answers back. For example: Cell reads: 1/1/2006 Answer using YEAR function: 06/1905 Answer that I want: 2006 My data comes from a CSV file, so I think maybe it is in a different date format than excel uses as a standard. Any suggestions on how to reformat this data or some other formula I can use to get the answer I am looking for? If the cell containing the date is not recognized as a date format then you might ...

Newbie here
Hi all. I'm new to the group and do very little with excel. I'd like to do the following just to to make my life easier at work. I know about conditional formatting, but what I want to do is a bit too complex for that, I think. Therefore I must need VBA but I've never used it before. Any pointers please? I am doing a sheet which will keep track of the training needs of a group of people. Some training needs to be done once every 6 months, some once a year and some just once every 2 years. So I have names on the left in the rows and each column refers to an item of training....

dates and ages
I have a list of names and birthdates in the format: Diane 5-2-00 Joey 2-9-01 Nancy 12-16-99 I would like to add a third column that calculates the ages of the persons listed based on todays date (in days). Does anyone have any idea how to do this? Thanks! mp =TODAY()-B1 format as a number -- Message posted from http://www.ExcelForum.com mp Check out The DATEDIF Function. Info at Chip Pearson's site. http://www.cpearson.com/excel/datedif.htm Gord Dibben Excel MVP On Tue, 22 Jun 2004 07:21:31 -0700, "mp" <anonymous@discussions.m...

Entering date in a cell so that it falls in specific way when sorted?
I have an entry that I know falls in February 2004 but that we don't have an exact date for as that information has been lost (ahem, _not_ may I say by me [thank goodness!] <lol>). Anyway, all the other entries for this date column are in the usu. m/d/yyyy format even though they have a custom setting that has it display differently. My problem is one I've run into many times before - how to have a, say, 2004.02 entry (for Feb.2004) sort between the last January 2004 one and the first February 2004 one? Is it possible to enter the Feb.2004 date in such a way that it will sort ...

Excel 97/2000 - Autofilter
Hi, Im trying to find my perfect date using Excel (haha). I have all my friends in a database, with Autofilter on. Since I'm male, I first filter on the column sex=f, than second filter is on the column married=n. This works ok, all my non-married female friends are showing. Next I filter on salary=top 3 and the result is empty! I know that my non-married, female friends are not in the salary top-3 of my database, but that is not what I'm looking for. Im looking for the top-3 salary unmarried girls! What do I do wrong, this way I always stay single ! Regards, Ronald Ps. Yes I know, ...

Phyical inventory count on parent items, how?
We are looking for a way to create a physical inventory on all items in a store database. With a small warehouse, we have Child Items associated with Parent Items. We would like to get a count of not just how many Parent Items or Child Items we have, but a combination of both. For example, If Parent item A contains 5 child items B. And if there are currently 2 parent items. we will get a total quantity of 10 child items with no indication to the available parent items in the report(they are all ready included in the child item quantity total of 10). Basically we would like to know t...

Date Calculations #3
Cell A1 contains accounting Period start Date say 1/2/04 A2 contains accounting period end date say 31/1/5. A3 contains a different start date say 1/4/04 and cell a4 contains different end date say 31/3/05. I need a formula to tell me how many of the days in 1/4/04-31/3/05 fal within the accounting period. Thanks in advance for your hel -- Message posted from http://www.ExcelForum.com try using the datevalue(date) function, it returns a number value and i the two are subtracted, it should work out to the number of days. so if you take the time frame and find out how many days are in it...

Inventory Dates #2
If I am to add all the shipments for all different equipment types by receipient and other criteria, eg =SUMIFS(RECORDS!TOTALS,RECORDS!MONTH,$A$2,RECORDS!EQUIPMENTTYPE,C$2,RECORDS!RECIPIENT,$A4) This formula is validating the receipient, the equipment type and even the month it was shipped. Adding totals of all shipment by item description. Our inventory starts on the 22nd of each month and ends on the 21st of the following month. Up to this point, I had to add a column to specify what month's inventory the shipment must be applicable to, otherwise is considering the month to start ...

Cell colour according to date
Hi, Is there a formula that would change the cell colour if a date in a cell was out of date afetr a set number of years. Example: Cell B9 contained date 140506 I then want the cell B9 to turn red when the date reaches 2 years later 140508. Thanks in advance Jason Let's assume that your example date is 14 May 2006. Click on B9 and: Format > Conditional Formatting... > Formula Is > =TODAY()-B9>731 then pick the colour red. -- Gary''s Student - gsnu200773 Use conditional formatting with a formula of =DATE(YEAR(B9)+2,MONTH(B9),DAY(B9))<TODAY() -- --- HTH Bob ...

Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...

Count value in a range across workbook
Using Excel 2003. Novice user with little VBA experience/knowledge. I am trying to have my workbook count each time a value is displayed in range A9:A18 on each sheet. A new sheet will be added each week and I do not want to have to update the formula for every sheet that one is added. Column A contains account numbers. Column C I would like to have the total amount of times the account number in A is found throughout the entire workbook. C2 displays the count result of account number is A2, C3 displays A3 counts...etc. There are approx 2000 account numbers in column A and some ar...

Date Ranges / Report / Displaying Data
Hi, I have a report named “rptLocal”. This report is made up of approx. 50 queries. How would I go about making it so that the report only displays data ranging between two dates? I would like make it so that the user would enter the date ranges into textboxes within a form “frmReport”, presses a command button and poof the report. Is this possible? And how? Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200708/1 50 queries in a report? Wow, that's a lot of different information. If you want each query to be filtered by ...

how to include real time & date on a worksheet
how to include real time & date on excel spread sheet I was supplied with a small macro that would enter the current date/time in a cell when invoked. Sub NOWTIME() ActiveCell.Value = Format(Now(), "mm-dd-yyyy h:mm:ss AM/PM") End Sub Is that what you want? Barb Reinhardt "nde" wrote: > how to include real time & date on excel spread sheet ...

Counting items from Table
Hi, I've created a table that has a number of columns, eg Item No Open/Closed Type 122 Open Letter 143 Closed Fax 156 Open Letter 234 Closed Letter 253 Open Fax And what I want to do is add up the number of items that are open and of type Letter. i.e Open AND Letter = 2 I've tried COUNTIF but that only works on one column, so any suggestion on how I can achieve this. This list will be added to so I would prefer the sum updates to be done automatically over a larger range than is currently present. Thanks in advance for...

running total between two dates
I am trying to have my spreadsheet sum up purchases that fall between (month) 12th to (month) 12th, and when/if a new purchasing month starts, restart summing the purchases. Column G are purchase amts. Column A are purchase dates. Originally, I just referenced month to month, with Column I formula: =MONTH(A5) etc., and my running total forumla: =IF(I4<I5,G5,IF(I5=I4,(G5+J4),0)) but because my purchasing dates are middle of the month, this is not working so well. I would like something I can fill down and not have to rewrite for each date. I've looked through the...

Turn off auto date recognition
Hi All, Is there a way to keep Excel from turning text that might represent a date into a date? For instance a user is entering text such as june20, march08. If we format the cells as text before entering it is fine until he tries to use the Replace feature. During the Replace process Excel overrides the text formatting. Is there anyway to turn this off? We forced it with an apostrophe, but that seems like a sloppy fix. Any suggustions would be appreciated. Thanks. Erin ...

Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly using the short date format in the regional and language settings, which, in my case is MM/DD/YYYY (reset at every boot). Unfortunately excel insists in interpreting that date as DD/MM/YYYY [Application.International(xlDateOrder)=1, it should be 0], disregarding my regional settings. The result is that all the dates are wrong or #VALUES! If this is not wierd enough, listen to this: it only happens after I reboot (when the date format is reset to MM/DD/YYYY). If subsequently I change the short date format in the regional se...

Date some qualifies
Ok, I need help with a formula (if you have time). If I have someone’s hire date and birth date, I need to calculate what date they will qualify for retirement, either equals age 60 plus years of service = 75 or age 65 (whichever is earlier). Thanks!! http://www.cpearson.com/excel/datedif.htm -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "sgwill" <sgwill@discussions.microsoft.com> wrote in message news:3C110E9F-CBF9-4CD6-B473-53C6FEDFB917@microsoft.com... > Ok, I need help with a formul...