| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
One way:
=SUMPRODUCT((A1:A8<>"")/COUNTIF(A1:A8,A1:A8&"")) Based on your sample the result is 5. -- Biff Microsoft Excel MVP "dj479794" <(E-Mail Removed)> wrote in message news  B4CEDE2-AA4F-4AE3-9187-(E-Mail Removed)...> (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.... > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news B4CEDE2-AA4F-4AE3-9187-(E-Mail Removed)...> > (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.... > > > > > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:5D9CE75C-7B75-40C5-91B0-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news B4CEDE2-AA4F-4AE3-9187-(E-Mail Removed)...>> > (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.... >> > >> > >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news:5D9CE75C-7B75-40C5-91B0-(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > >> news B4CEDE2-AA4F-4AE3-9187-(E-Mail Removed)...> >> > (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.... > >> > > >> > > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:455A8DA4-0742-43CC-BED0-(E-Mail Removed)... >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" <(E-Mail Removed)> wrote in message >> news:5D9CE75C-7B75-40C5-91B0-(E-Mail Removed)... >> > 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" <(E-Mail Removed)> wrote in message >> >> news B4CEDE2-AA4F-4AE3-9187-(E-Mail Removed)...>> >> > (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.... >> >> > >> >> > >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news:455A8DA4-0742-43CC-BED0-(E-Mail Removed)... > >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" <(E-Mail Removed)> wrote in message > >> news:5D9CE75C-7B75-40C5-91B0-(E-Mail Removed)... > >> > 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" <(E-Mail Removed)> wrote in message > >> >> news B4CEDE2-AA4F-4AE3-9187-(E-Mail Removed)...> >> >> > (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.... > >> >> > > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:CF3F4AF1-B703-4FEB-836B-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news:455A8DA4-0742-43CC-BED0-(E-Mail Removed)... >> >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" <(E-Mail Removed)> wrote in message >> >> news:5D9CE75C-7B75-40C5-91B0-(E-Mail Removed)... >> >> > 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" <(E-Mail Removed)> wrote in message >> >> >> news B4CEDE2-AA4F-4AE3-9187-(E-Mail Removed)...>> >> >> > (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.... >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Formula Count dates multiple criteria | GPearson | Microsoft Excel Worksheet Functions | 3 | 11th Nov 2009 09:22 PM |
| count dates in multiple columns | =?Utf-8?B?T3J0bGV5?= | Microsoft Access Reports | 3 | 7th May 2007 02:14 PM |
| Re: Count multiple start/end work dates of an employee | John Spencer | Microsoft Access Queries | 0 | 12th Dec 2006 07:27 PM |
| Count values in rows depending on dates. | =?Utf-8?B?UWFzcGVj?= | Microsoft Excel Worksheet Functions | 1 | 24th Jan 2005 09:11 PM |
| Count based on multiple conditions (between two dates) | Joe Miller | Microsoft Excel Misc | 3 | 21st Jul 2003 01:12 PM |
