COUNTIF using amount of time

I have some data with various time intervals in mm:ss.ms and I want to
use the COUNTIF function to count how many of them fall between 1-2
minutes, 2-3 minutes, 3-4 minutes, etc., but I keep getting a count of
all the values. Am I comparing the time intervals wrong?

For example, the data (in the A column) is:
01:19.666
01:13.287
01:28.308
02:29.872
05:36.658
01:01.770

My formula for counting those between 1-2 minutes:
=COUNTIF(A:A,">=00:01.000&&<00:02.000")

The returned value is 6.

Any help would be appreciated,
Dennis

0
1/23/2007 8:08:13 PM
excel 39879 articles. 2 followers. Follow

3 Replies
475 Views

Similar Articles

[PageSpeed] 54

Dennis,

For 1 to 2 minutes, try
=SUMPRODUCT((A2:A7>=TIMEVALUE("01:00.0"))*(A2:A7<TIMEVALUE("02:00.0")))

You've put a boolean (TRUE/FALSE) in the COUNTIF, but it wants actual 
comparison values.  We sit outside the Excel building and bawl for SUMIF and 
COUNTIF functions into which we can put booleans, but are told we won't get 
them until the year 2057.  So we have to write these geeky SUMPRODUCT 
functions.
-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"dman" <dennis.man@gmail.com> wrote in message 
news:1169582893.843679.27410@l53g2000cwa.googlegroups.com...
>I have some data with various time intervals in mm:ss.ms and I want to
> use the COUNTIF function to count how many of them fall between 1-2
> minutes, 2-3 minutes, 3-4 minutes, etc., but I keep getting a count of
> all the values. Am I comparing the time intervals wrong?
>
> For example, the data (in the A column) is:
> 01:19.666
> 01:13.287
> 01:28.308
> 02:29.872
> 05:36.658
> 01:01.770
>
> My formula for counting those between 1-2 minutes:
> =COUNTIF(A:A,">=00:01.000&&<00:02.000")
>
> The returned value is 6.
>
> Any help would be appreciated,
> Dennis
> 


0
someone798 (944)
1/23/2007 8:47:59 PM
=COUNTIF(A1:A6,">=01:00.00")-COUNTIF(A1:A6,">02:00.00")

(# of entries >= 1 minute) - (# of entries over 2 minutes)
= # of entries between 1 & 2 minutes.

HTH


"dman" <dennis.man@gmail.com> wrote in message 
news:1169582893.843679.27410@l53g2000cwa.googlegroups.com...
>I have some data with various time intervals in mm:ss.ms and I want to
> use the COUNTIF function to count how many of them fall between 1-2
> minutes, 2-3 minutes, 3-4 minutes, etc., but I keep getting a count of
> all the values. Am I comparing the time intervals wrong?
>
> For example, the data (in the A column) is:
> 01:19.666
> 01:13.287
> 01:28.308
> 02:29.872
> 05:36.658
> 01:01.770
>
> My formula for counting those between 1-2 minutes:
> =COUNTIF(A:A,">=00:01.000&&<00:02.000")
>
> The returned value is 6.
>
> Any help would be appreciated,
> Dennis
> 


0
1/23/2007 9:12:14 PM
Thanks for everyone's help. I tried all the solutions and these two
worked.
(Oh and yeah, all the times I'm counting are under an hour).

=SUMPRODUCT(--(MINUTE(A1:A10)=1))
=SUMPRODUCT((MOD(A1:A10,1)>=1/(24*60))*(MOD(A1:A10,1)<2/(24*60)))

Dennis

0
1/24/2007 1:34:40 PM
Reply:

Similar Artilces:

using master page in publication with text boxes
When I use a master page that has text boxes in a publication, how do I get text in the text boxes? John wrote: > When I use a master page that has text boxes in a publication, how do > I get text in the text boxes? =================================== Go to...View / Master Page. Select the textbox. Position your cursor where you want the text to begin. Begin typing. These exact text boxes will appear on every page. -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech sup...

Read Text File into Excel Using VBA
Hi All, I'm a new VBA programmer. I know how to pull an entire text file into an Excel Spreadsheet, but I only want specific information from the text file not the entire text file. What I have is about 25 text files stored in a folder, let's say C:\test. Each file is named by a property address as follows: 209 MAIN ST.txt 213 MAIN ST.txt 111 ELM ST.txt 2356 WOOD AVE.txt On the 11th row of each file is as follows: Property Address:209 MAIN ST On the 31st row of each file is as follows: Total Value:30500 What I would like to do is read each file located in the "C:\test folder...

Which Date does the system use for Date filter in Acvitities view?
CRM 3.0 Activity window in web client offers Date as a built in filter criteria. Which date does it use? It does not appear to use Due Date as the filter. The built in filter of Today vs Overdue does not bring up different result. How does this thing work? I ran into the same problem and found a hot fix that resolved the issue. You will need to contact Microsoft to have them send you the hot fix. http://support.microsoft.com/kb/917822 -M "Winnie" <Winnie@discussions.microsoft.com> wrote in message news:A6C32AE4-C44B-4B45-97E9-1E616E83C105@microsoft.com... CRM 3.0...

Add more than one series to a pivot chart using VB MS Access continued...
I'm trying to programmatically create a stacked bar pivot chart. Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've created the chart. However, it doesn't distinguish between the different values for the series. Does anyone have any suggestions on how to create a chart using one column containing three values for the series? Essentially, this is a continuation of a previous post: http://groups.google.com/group/microsoft.public.access.formscoding/browse_thread/thread/e23f3506a6d561a0/674295d410a71cf9%23674295d410a71cf9" Any help is greatly appre...

2003 missing message ID unless using Exchange sever
My original post seems to have dissappeared... Ah well, here are the replies, anyone know a fix? Message-IDs are generated by Exchange server if you are=20 using Outlook 2003 in an exchange server environment.=20 But if you are using Outlook 2003 in a non-exchange=20 environment (generic SMTP server) then server logs show=20 no message_ID for anything sent from Outlook 2003,=20 previous versions of Outlook have Message-IDs on the same=20 server. see http://www.slipstick.com/emo/2003/up031211.htm, under=20 outlook 2003 messages suspected as spam. Fair enough if people want to turn Message-...

Is there a Time picker?
I've read numerous posts on numerous boards about time formating, none of which provide a direction to a true time picker. There are many a date/time picker references. It would seem to be the answere to many formating questions and would be a life saver to me. Anyone know of such a bit of code? Thanks Barry You could build one. In a fresh UserForm place a textbox (TextBox1) and try pasting the below code ...Use up/down arrows to adjust the time Private Sub UserForm_Activate() Me.TextBox1 = Format(Now, "hh:mm") End Sub Private Sub TextBox1_KeyDo...

Changing the Default Activity Time
Hi I would like to change the default time shown when creating new Activities. The current default is 30 minutes. We woul like to reduce this to 5 mins etc depending on the Activity. Michael, You cannot do this in CRM by using the standard tools. You could try and edit the activity aspx page and use some client side scripting to change the picklist value at runtime. Regards, Rob Bakkers, Avanade Netherlands "Michael" wrote: > Hi > > I would like to change the default time shown when creating new Activities. > The current default is 30 minutes. We woul like...

time format from hh:mm:ss to h:mm
I have been trying forever and no luck at all!!! I have changed the the whole column under format cell/Number/Category/time and selected 13:30 (english USA) When I then try to do a mail merge in word 2003 the merged field still comes up hh:mm:ss which I don't want??? Any help greatly appreaciated. Unfortunately, mail merge doesn't look at the cell's format, only its number type. Either create a text field, using a formula like: =text(a1,"hh:mm") or specify the format in your Word document. Regards, Fred "Diana" <Diana@discussio...

use of Randbetween() in Excel
Hi to everyone. I had 10 columns in Excel. A1 B1 C1 ……J1 ..... … … ……… … … … ……… A50 B50 C50 j50 Each column uses the Randbetween() function to create integers 1-100, that is Randbetween(1,100). Is there anyway to use somehow the function so the integers in every row to be different? (I mean: A1 <> B1<> C1…….<>J1, A2 <> B2 <> C2…..<> J2,……………, A50 <> B50 <> C50 …….<> j50) Thank you. http://...

How do I use Outlook 2000???
I think when I installed Outlook 2000, there was actually a Trash folder. I've since installed SP3 and security updates and now there isn't! There's a Deleted Items folder, and I can't rename it. Outlook sorts the folders on the folder list alphabetically. Is this the only option??? I don't want them in this order. I'd like to see my Inbox first, Trash last, etc. I want them in a specific order. We're migrating from Novell GroupWise and the folders in GroupWise were not in alphabetical order. I want to put the Outlook folders in the same order. It would be ...

Date/Time 01-04-08
I have a qeury field that has long date and time such as 1/4/2007 6:45:05PM what I need to do is subtract 5 hours from the time, thats it. Is there a way to do that? The Query is connecte direct to a sql database. I know enough to not mess with sql directly. Any ideas? John wrote: > I have a qeury field that has long date and time such as 1/4/2007 > 6:45:05PM what I need to do is subtract 5 hours from the time, thats > it. Is there a way to do that? The Query is connecte direct to a > sql database. I know enough to not mess with sql directly. Any > ideas? DateAdd(&q...

Using the highest count for the field name in a query
I have a table that list a count of peoples visits along with the State City and 5 digit Zip Code. I would like to query this data so I can get a total(Sum) of peoples visits by their 5 digit zip code. This is difficult because some Zip Codes can have more than 1 town associated with that 5 digit zip code. I would like the total for all trips in that zip code but have only the city or town with the most visits listed in the city field. For instance I would like the table below City State Trips 5 Digit Zip Anytown OH 1 18555 Anytown OH 2 18555 Farmtown...

date and time calculations
What would my formula and format of cells be for calculating hours between dates and times. Ex. start time Jan 2, 04 8 am to end time Jan 4, 04 5 pm. I DO NOT want my times or answers in military time. If you could please give me an example of how to format each cell and the formula for each cell, it would be greatly appreciated. Thank you. Matilda In the UK I'd enter the start time as 2/1/04 8: Excel will format this as 02/01/04 8:00 or whatever your default setting is. Notice the space between the date and the start time. The formula for calulating the hours is =IF(I8<I7,(I8+...

Resolution time
What is the best way to summarize different types of time spent on cases? Contract handling is not an option because contract is eternal. I have a customer and they record actual time, billable time and third (customized attribute in Incident) type: recorded time. They use email, appointment and task -activities within cases. We get different duration-fields into reports but cannot find any logic there ;( ...

Scheduling an email SEND time and date...
Am using MS Office/Outlook 2002. Just purchased the upgrade to Office/Outlook 2003. Will either of these allow an email to be "scheduled" for sending at a future time and date? That is, I can create the email now, then tell it to "send" at 9am tomorrow morning. Is it correct to assume that 1) it would look at the system clock (9am) to see when the time occurs, so the system date/clock must be correct. 2) even though I have an "always on" DSL connection, Outlook must be OPEN (displayed on the monitor) for it to be able to send...or is Outlook running in the ba...

What are the common issues that can cause slow boot times of Windows CE6 Images?
I am relatively new to Platform Builder, and whilst I am able to produce nk. bin files, they boot very slowly, 80-100 seconds, so I think there may be some checkbox somewhere that I need to set (or clear)! I've already removed kitl, profiling, etc in the project settings, and set the project to 'release build' & 'ship'. When I looked at the startup event log (in debug), there doesn't appear to be any specific point where it is slow. The log pretty much scrolls all the way through with no major pauses. One thing I found strange was that although the nk....

Wrong time in Outlook
Hi, I've got a really strange problem. My Mails in Outlook have all the wrong time - 9 hours back. This affects incoming and outgoing mails. It can't be a matter of the e-mail-provider because I've got accounts in gmx, web, yahoo, gmail and all have the same problem. Where can outlook get the wrong time ? The system time is correct of course. Thanks for your help. Ralf is the system time zone correct? whois shows your posting IP is in Germany... and windows defaults to pacific time zone so that would account for the difference. -- Diane Poremsky [MVP - Outlook] Author, Te...

Error while synchronizing with net time server
Vista Ultimate SP2 I just noticed that my machine will not synchronize with any of the Internet time servers. It's set to do so automatically, but I see an error saying "An error occurred while Windows was synchronizing with [server name]. This happens is I try to sync manually, and it happens with all server (i.e. time.windows.com, time.nist.gov, time-a.nist.gov, time-b.nist.gov, time-nw.nist.gov). Suggestions? Thanks. Ken Isaacson SILENT COUNSEL, a legal thriller www.KenIsaacson.com Ken Isaacson wrote: > Vista Ultimate SP2 > > I just noticed...

Do people use CAS assembly declarations or it just a feature that is used very rarely
Hi! I read about this CAS assembly declarations and wonder if this is really used to any extent or if this is just a feature that peope don't bother to use. I can understand that you can adjust the assembly permission by using the CAS assembly declarations to tell the assembly exact what permission it should have or not have but I don't think people bother to add any kind of CAS assembly declarations to the assembly. //Tony ...

DO YOU USE A PASSWORD (IE) PASSPORT IN MONEY?
I have been a Microsoft Money user since 1995 and have updated the product annually. I have NEVER allowed a password on my Money files. I do NOT link my files onto the internet servers. My Money program is strickly on MY personal computer. I do allow downloads of investment information weekly, but most everything else is entered manually.I DO utilize my bank and credit card company's web site and phones to verify and maintain my daily account balances for those items. I intend to maintain this posture, at least for the coming year. While I do occasionally have some problems with the prog...

Using Multiple Exchange Accounts
Software:----- Exchange Server 2003, Outlook 2003 Situation:----- User Jane has two mailboxes, a default (Jane) and secondary (Sales), configured in her Outlook profile. The secondary mailbox (Sales) was added through: Tools|E-mail Accounts|View or Change Existing E-Mail Accounts|Microsoft Exchange Server|Change|More Settings|Advanced|Mailboxes|Add Question:----- If Jane is focused on the Inbox folder of the Sales mailbox and clicks New Mail Message, the From field of the new message will automatically populate with Jane instead of Sales. She must manually change the From field to S...

Another user using Microsoft Office
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi I've just installed a new copy of Office 2008 for Mac. When running up it tells me that another person is using this software and that I have exceeded the number of valid users. This only occurs when I'm on our office network. There is infact another person in our office using the same software but this was bought some time ago and has a completely different product ID number (well the last 5 digits are different - rest the same??). He has the same problem if I start up my software first and then he tries to ...

using the time structure with MFC
Hi, Coming from C, how could I translate this into MFC based code (using CString)? char szTime[65], szDate[65]; time_t t = time(0); struct tm* tm = localtime(&t); strftime(szDate, 64, "%Y%m%d", tm); strftime(szTime, 64, "%H%M%S", tm); CString strTime, strDate; Thanks CTime Time = CTime::GetCurrentTime(); CString strDate = Time.Format("%Y%m%d"); CString strTime = Time.Format("%H%M%S"); AliR. "markww" <markww@gmail.com> wrote in message news:1156883820.948360.39250@m79g200...

How to determine what kind of licensekey used?
I have a alphanumeric licensekey which I think is used on an installation. But when I open the 'deploymentmanager' on the server, the licensekey is all numeric. Is the licensekey somehow changed into another key when used? How do I determine is my licensekey is a TRIAL or a MSDN key when I can't see the alphanumeric licensekey? Thanks... In deployment manager , when you click on License , there are three column. License Type ..............Number ...........................LicenseKey Enterprise ---------------------12--------------------------------- (license key is changed to nu...

File size when using background image?
I've been digging through google as well as MS's support site and the various newsgroups, public and private, and haven't found any suggestions for this. I've found the question asked, but never answered... Is there any way when putting a background image in an excel file to keep it from shooting the file size up to several meg? An empty XLS file that starts out as 13.5K turns out to be 3.4 MB with the inclusion of a 10k GIF as a background. Thanks for any suggestions. Just looking into an option for someone who wanted a background image for a template for some documents th...