Countif function using times

Hello All,

I'm using Windows and Excel XP.

I am trying to count the number of occurences of times in a certain range. 
For example:

                A
-----------------------
1         0:15:03             (0 hours, 15 minutes and 3 seconds)
2         0:22:45
3         0:17:33
4         0:13:22
5         0:21:00
6         0:16:01

I would like to find the number of occurences that fall between the times of 
0:15:00 and 20:00 (which would be 3).   I am having a problem writing the 
formula =COUNTIF(A1:A6,">=0:15:00<0:20:00").  It is returning the #VALUE! 
error.  Not sure if the time values in A1:A6 have to be converted to a
serial number or not.    Any help would be appreciated.

Michael 


0
windme (104)
11/26/2004 1:58:05 PM
excel 39879 articles. 2 followers. Follow

2 Replies
498 Views

Similar Articles

[PageSpeed] 51

try
=sumproduct((a3:a200>=a1)*(a3:a200<=a2))

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Mike" <windme@cox.net> wrote in message
news:6EGpd.6866$T02.3715@lakeread06...
> Hello All,
>
> I'm using Windows and Excel XP.
>
> I am trying to count the number of occurences of times in a certain range.
> For example:
>
>                 A
> -----------------------
> 1         0:15:03             (0 hours, 15 minutes and 3 seconds)
> 2         0:22:45
> 3         0:17:33
> 4         0:13:22
> 5         0:21:00
> 6         0:16:01
>
> I would like to find the number of occurences that fall between the times
of
> 0:15:00 and 20:00 (which would be 3).   I am having a problem writing the
> formula =COUNTIF(A1:A6,">=0:15:00<0:20:00").  It is returning the #VALUE!
> error.  Not sure if the time values in A1:A6 have to be converted to a
> serial number or not.    Any help would be appreciated.
>
> Michael
>
>


0
Don
11/26/2004 2:18:10 PM
one way:

    =COUNTIF(A:A,">=" & TIME(0,15,0)) - COUNTIF(A:A,">=" & TIME(0,20,0))


In article <6EGpd.6866$T02.3715@lakeread06>, "Mike" <windme@cox.net> 
wrote:

> Hello All,
> 
> I'm using Windows and Excel XP.
> 
> I am trying to count the number of occurences of times in a certain range. 
> For example:
> 
>                 A
> -----------------------
> 1         0:15:03             (0 hours, 15 minutes and 3 seconds)
> 2         0:22:45
> 3         0:17:33
> 4         0:13:22
> 5         0:21:00
> 6         0:16:01
> 
> I would like to find the number of occurences that fall between the times of 
> 0:15:00 and 20:00 (which would be 3).   I am having a problem writing the 
> formula =COUNTIF(A1:A6,">=0:15:00<0:20:00").  It is returning the #VALUE! 
> error.  Not sure if the time values in A1:A6 have to be converted to a
> serial number or not.    Any help would be appreciated.
> 
> Michael
0
jemcgimpsey (6723)
11/26/2004 2:18:45 PM
Reply:

Similar Artilces:

View pointer in static function Help!!!
Help please , i need to implement a static function in CView derived class that must return to CDoc derived class the active view pointer...how can i do? i tried something similar CSdicap10View* CSdicap10View::GetView() { CSdicap10View* pView = GetActiveView(); return pView; } it return error GetActiveView undeclared identifier or also CSdicap10View* CSdicap10View::GetView() { return this; } but it return static member functions don't have this pointer and also CSdicap10View* CSdicap10View::GetView() { CFrameWnd * pFrame = (CFrameWnd *)(AfxGetApp()->m_pMa...

Forwarding Using Mail Enabled Users
Are there any differences forwarding mail to an external recipient using a mail enabled user vs a mailbox enabled user forwarding to a contact aside from the fact that the mail enabled user doesn't have a mailbox? In news:eRPSivG0FHA.3408@TK2MSFTNGP09.phx.gbl, Steve Fukumoto <steve.fukumoto@necsam.com> typed: > Are there any differences forwarding mail to an external recipient > using a mail enabled user vs a mailbox enabled user forwarding to a > contact aside from the fact that the mail enabled user doesn't have a > mailbox? A mail-enabled user has an external...

Use the same name on multiple sheets in same workbook
I am new to Excel 2002. In prior versions I could create sheet specific names easily. With Excel 2002 every time I try to re use a name on another sheet (same workbook) the name gets moved to the new sheet. I use several workbooks (one book for sales, one for cost of goods, etc), with 12 sheets named for each month of the year. Each sheet is a duplicate of the first. As the year progresses I fill in the data at the end of each month. In the past I used the same names(Gross.Sales for example) on each sheet in the workbook. I then can build monthly summary reports using these names...

Ccur function
I am using the Ccur function to convert a number to a currency type. It works fine except in the cases where the number is negative. Is there a way to get a negative currency value? Correction: The case it fails on is this Ccur( 0 & [number] ). If the field is blank...make it $0.00, else make it $number. Fails when [number] is negative >-----Original Message----- >I am using the Ccur function to convert a number to a >currency type. It works fine except in the cases where the >number is negative. Is there a way to get a negative >currency value? >. > Thi...

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...

Countif Interior Blue or Red
i have looked around the answers for this but cant seem to get any to work. i am looking for a formula to look in A1:A160 and count the cells if they are blue or Red the text in them is irrelivant Thanks in advance see: http://www.cpearson.com/excel/colors.htm -- Gary''s Student - gsnu200770 "Arnie" wrote: > i have looked around the answers for this but cant seem to get any to work. i > am looking for a formula to look in A1:A160 and count the cells if they are > blue or Red the text in them is irrelivant > > Thanks in advance See http://www.xldy...

COUNTIFS with a date for criteria...
I want to count all the entries in column N that occurred between a range of dates specified in the other criteria. The dates are obviously in column B. I use this currently- COUNTIFS(Data!N:N,"=Option",Data!B:B,">=1/1/2009",Data!B:B,"<=2/28/2009") However, I use this similar function in multiple cells, all counting different attributes. Rather than going into each cell and changing the search dates, I wanted a criteria to be a variable that can be entered into a different set of cells... like this. COUNTIFS(Data!N:N,"=Option",D...

Error when send email from campaign response using template through workflow
Hello, There is an invalid argument error shown in workflow records if send email from campaign response using template. Is it impossible to send email in this way ? ...

Roundin specific input time
Please, I need me. I built a time management database and would like to round specific times up or down. For example whenever someone in time is between 7:16am to 7:33am, I would like it rounded to 7:30am, in times between 8:16am to 8:33am to 8:30am and all other in times around thoses ranges not to be rounded. I have some individuals reporting at 7:30am and some at 8:30am, would prefer the code to also round 8:16am to 8:33am as 8:30am as well. Out time ranges between 4:00pm and 4:10pm round to 4:00pm and ranges 5:00pm to 5:10pm rounded to 5:00pm and all other in times around thoses ran...

Invalid Argument Error when using 1/1/08 as a date
Hi all, Not sure if this is a known issue - can anyone else create a record that contains a date field with the value of 1/1/08 or 1/1/07, save it and then open it again without getting an "Invalid Argument" error. I am able to produce this error across different environments, different entities and different date fields. Would be interesting to hear more about this. Thanks Hi there, Just to let you know, I just created a task in CRM 4 and set the due date to 1/1/08 and it worked without any issues. This is a field displayed as a Date/Time box, on not just Date. I don...

Use of InstallShield
Hi, I have got InstallShield with VC++, and I would like to use it to create a setup for various types of project, e.g. a game, activeX control, etc. However the project wizard only allows you to choose an .exe, and it makes you choose between 'database application', 'finance application', etc. What if my product isn't an .exe, say it's an .ocx, or what if it isn't one of these predefined types of program? I've no idea on where to start with the blank project option. I added my .ocx to the 'components' view and then tried pressing Ctrl+F5, but a...

Money always forces 4 day lead time for electronic payments
My bank and I were trying to figure out why, when I order an online = payment for asap, it takes 4 (sometimes 5) business days to get paid, = but if I do it online at their website, it can be done the next business = day, or at most 2 days. Once I sync money by connecting to the bank = through money, it downloads the payment and has the right day on it. Question: Why does money not allow you to choose an earlier date for = epays (as opposed to check pays)? Any setting to change this behavior? = Thanks The bank actually determines that. If Money is using the bank's bill pay service, t...

Conditional formating using VBA
Hello I have to format cells (I4:J37) if cells (I43:J76) are: 100%-91% (green) 90%-76% (blue) 75%-50% (yellow) <50% (red) How do I write that in VBA? (I am new to VBA so any help would be appreciated) Many thanks Tracey Hi Tracey, I asume your users will enter a value in the range("I43:J76"). In that case you case use the Worksheet_change(Byval Target as Range function like so: Private Sub Worksheet_Change(ByVal Target As Range) ' Check if only 1 cells value is changed If Target.Cells.Count > 1 Then Exit Sub ' Check if changed c...

what function would I use here?
In rows 1 to 192 I have a letter in just one of the columns D,E,F,G,H,or I. I want to fill in column C so that it will have the letter that is in column D thru I. What is the function that I use? I tried =MAX(d2:i2) but that doesn't work. I suppose the MAX function is for numbers only. Is it a FIND function or something different? Thanks in advance. -- Larry Smith "In this country anyone can grow up to be President. That's the risk you take." Adlai Stevenson You could put this formula in C2 (for example): =3DD2&E2&F2&G2&H2&I2 and then c...

Word 2003: Possible to Use both Forms and Tracked Changes Protect
I've not been able to figure out a way to protect a document so that one section is protected for filling in forms only, and another section is protected in tracked changes mode. I want to create better contract templates for use by our sales teams (thus the forms protection - they can only fill in the gray fields and not alter other sections of the template), while allowing our clients to made modifications to the terms in the subsequent pages. I find Word protection features very limited. Hopefully it's better in 2007. Thank you. A document can only have one type ...

Connecting internal ide HDD to Vista PC using caddy
I've just tried connecting an old HDD from a XP PC to my Vista PC via a caddy but it says USB device not recognised. Tried it with another XP machine and it is recognised. can I get the Vista PC to recognise it? Also with the various user folders one of them ( I guess it's because it had a password on the original machine) won't allow access. Is there a way of accessing these files? Trevor wrote: > I've just tried connecting an old HDD from a XP PC to my Vista PC > via a caddy but it says USB device not recognised. Tried it with > another XP machine ...

Thesaurus takes long time to load
When working in Word and trying to use the Thesaurus it is now taking a long time for the Thesaurus pane to open up to use. What could be causing this and what would the solution be to fix it? Microsoft Word 2003 SP 3 ...

Excel functions #3
Hi. How can i change the "," symbol with the ";" in the syntax of an Excel Function ? For examp. ceiling(A1,3) to be ceiling(A1;3) Thank you. Change in the Regional settings of the control panel, on the Regional Options click the Customize button, that will get you there. -- __________________________________ HTH Bob "nikos" <nikos@discussions.microsoft.com> wrote in message news:B673ECB4-472B-4590-8D10-B35634393DB4@microsoft.com... > Hi. > How can i change the "," symbol with the ";" in the syntax of an Excel > Function...

excel 2000
I recently read a posting about being able to control the tab key t jump to any specified cell by using a named range... this is great! I it possible to do the same thing using text boxes -- Message posted from http://www.ExcelForum.com ...

Lookup function for two variables and return another cell?
Hi! I am looking for some help. Seems pretty simple but I can't seem to figure it out. I know I'll need a lookup function and an "AND" function of some sort... Take a look and let me know what you think. row/column A B C D 1 New SSN 1st signed Blue 2 2 New SSN Calc - not signed Green 1 3 Previous SSN 1st signed Red 3 So I need help finding the combination of "New ...

My onload gives message that the function is not a member
<%@ Page Language="VB" MasterPageFile="~/Main.master" AutoEventWireup="false" CodeFile="SecondChurchDedication.aspx.vb" Inherits="History_SecondChurchDedication" title="Untitled Page" %> <asp:Content ID="Content8" ContentPlaceHolderID="BottomImageCPH" Runat="Server"> <center> <iframe id="DocumentsFrame" runat="server" src="SecondChurchDedication.pdf" onload="setWidthHeight(this);">Your browser does not support iframes.</...

only allow one enititly to execute code at a time
I have a function that MUST be executed on its own, i.e. there cannot be multiple calls to the function, when the function is executing, another call to it must wait for the 1st call to return first. (if that makes sence). I have tried to do this by placing a BOOL value that is set to true when the function is entered, and false when the function exits, and check ing the value before the calls, but some how, it is still allowing multiple instances of execution. Is there any other way to stop this? I have several threads that all call this function and sometimes they overlap causeing the pro...

how to do time zone adjustment?
Hi, Any class in mfc that I can use to perform time zone adjustment? THanks. Raymond. news.ms.com <ryiu@marinemagnetics.com> wrote in message news:eLQt4SMlDHA.424@TK2MSFTNGP10.phx.gbl... > Hi, > > Any class in mfc that I can use to perform time zone adjustment? THanks. Depending on how much time zone adjustment you want, ie, truly international, a few lines of code should suffice. There are some complications when dealing with the U.S. where some states don't observe DST, so timezone differences are 'different' during different times of year. All of this is p...

Concananate Function for Multiple Rows
Everyone, I am looking to take text data from Column 1, Column 3, and add text after these values for every row in my spreadsheet. Is there a universal equation that I can use instead of having to insert values from each row individually using the Concananate function??? Thanks for the response mardskee wrote... >I am looking to take text data from Column 1, Column 3, and add text after >these values for every row in my spreadsheet. Is there a universal equation >that I can use instead of having to insert values from each row individually >using the Concananate function?...

How to Publish a Public Function (vb module) for use in other mdbs
Hi All, I created a Public function in a particular mdb and I want to make it available to other Access db's when running queries. How do I "publish" a module so that it's available as a default whenever I open/create a new database? Is there some sort of blank "template" db that it needs to go into, or something else. Can't for the life of me find it in the VB Help that goes with Access. Thanks! Hi Chris, Here you go: Using Database Library Files in Your Access Application http://www.vb123.com/toolshed/07_access/libraries...