| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count dates with multiple values?
(Excel 2003)
I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count dates with multiple values?
One way:
=SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... (Excel 2003) I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count dates with multiple values?
This array gave me an ouptut of zero. I must be doing something wrong. Any
ideas based on a result of zero? "T. Valko" wrote: One way: =SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... (Excel 2003) I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count dates with multiple values?
Are you using the correct range, did you just copy Biff's formula which
count A1:A8 and while your values are somewhere else? -- Regards, Peo Sjoblom "dj479794" wrote in message ... This array gave me an ouptut of zero. I must be doing something wrong. Any ideas based on a result of zero? "T. Valko" wrote: One way: =SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... (Excel 2003) I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count dates with multiple values?
I changed it to my range A3:A707 replaced A1:A8
"Peo Sjoblom" wrote: Are you using the correct range, did you just copy Biff's formula which count A1:A8 and while your values are somewhere else? -- Regards, Peo Sjoblom "dj479794" wrote in message ... This array gave me an ouptut of zero. I must be doing something wrong. Any ideas based on a result of zero? "T. Valko" wrote: One way: =SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... (Excel 2003) I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count dates with multiple values?
Does the formula still return 0?
If so, hmmm.... Try this one. This one will count NUMBERS only. =COUNT(1/FREQUENCY(A3:A707,A3:A707)) -- Biff Microsoft Excel MVP "dj479794" wrote in message ... I changed it to my range A3:A707 replaced A1:A8 "Peo Sjoblom" wrote: Are you using the correct range, did you just copy Biff's formula which count A1:A8 and while your values are somewhere else? -- Regards, Peo Sjoblom "dj479794" wrote in message ... This array gave me an ouptut of zero. I must be doing something wrong. Any ideas based on a result of zero? "T. Valko" wrote: One way: =SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... (Excel 2003) I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count dates with multiple values?
Ok. that is wierd. Your second formula worked. but the dates are stored as
dates and not numbers. oh well. it works thats whats matter. THANKS! "T. Valko" wrote: Does the formula still return 0? If so, hmmm.... Try this one. This one will count NUMBERS only. =COUNT(1/FREQUENCY(A3:A707,A3:A707)) -- Biff Microsoft Excel MVP "dj479794" wrote in message ... I changed it to my range A3:A707 replaced A1:A8 "Peo Sjoblom" wrote: Are you using the correct range, did you just copy Biff's formula which count A1:A8 and while your values are somewhere else? -- Regards, Peo Sjoblom "dj479794" wrote in message ... This array gave me an ouptut of zero. I must be doing something wrong. Any ideas based on a result of zero? "T. Valko" wrote: One way: =SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... (Excel 2003) I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count dates with multiple values?
Dates are really just numbers formatted to look like dates.
For example, the date: 9/25/2007 has an underlying value of 39350. The first formula I suggested will count *all* uniques, both TEXT and NUMBERS. The second formula I suggested will count *only* unique numbers. Either formula should work. Can't understand why the first one returns a 0. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... Ok. that is wierd. Your second formula worked. but the dates are stored as dates and not numbers. oh well. it works thats whats matter. THANKS! "T. Valko" wrote: Does the formula still return 0? If so, hmmm.... Try this one. This one will count NUMBERS only. =COUNT(1/FREQUENCY(A3:A707,A3:A707)) -- Biff Microsoft Excel MVP "dj479794" wrote in message ... I changed it to my range A3:A707 replaced A1:A8 "Peo Sjoblom" wrote: Are you using the correct range, did you just copy Biff's formula which count A1:A8 and while your values are somewhere else? -- Regards, Peo Sjoblom "dj479794" wrote in message ... This array gave me an ouptut of zero. I must be doing something wrong. Any ideas based on a result of zero? "T. Valko" wrote: One way: =SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" wrote in message ... (Excel 2003) I have dates in a column. Some days from start to end are missing and some show up multiple times. I just want a count of how may days are in the list without counting the same date more than once or counting dates that are not in the list. 7/1/07 7/2/07 7/2/07 7/2/07 7/4/07 7/4/07 9/13/07 9/15/07.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count values across multiple columns? | Excel Worksheet Functions | |||
| Simple Way to Count the Number of Duplicate Dates on Multiple Worksheets | Excel Discussion (Misc queries) | |||
| Count on multiple values with duplicate rows | Excel Worksheet Functions | |||
| Count occurances of multiple values | Excel Worksheet Functions | |||
| how do i count values based on multiple criteria | Excel Worksheet Functions | |||
Linear Mode
