Problem with Range

Hello All,

Using Windows & Excel XP.

I have a worksheet that has times located in every other column, A1:A30, 
C1:C30, E1:E30.  I then name the range "times".  I want to find the count of 
times that are between 0:30:00 and 0:39:59 (30 and 39:59 minutes).  I write 
the formula:
=COUNTIF(times,">=" & TIME(0,30,0)) - COUNTIF(times,">=" & TIME(0,39,59)) 
but get the error #VALUE!

I have tried writing a formula for times in one column and consecutive 
columns and it gives the correct count, it is just when the times  in every 
other column that the formula doesn't work.  I would apprecicate any help in 
getting the formula to work for the times located in every other column.

Thanks in advance,
Michael 


0
windme (104)
2/14/2005 5:15:08 PM
excel 39879 articles. 2 followers. Follow

2 Replies
651 Views

Similar Articles

[PageSpeed] 50

Name the whole range "Times" (A1:E30), then use this formula:
=SUMPRODUCT((Times>=TIME(0,30,0))*(Times<=TIME(0,39,59))*(MOD(COLUMN(Times),
2)=1))
which will pick up every other column.

Bob Umlas
Excel MVP

"Mike" <windme@cox.net> wrote in message
news:x05Qd.63290$jn.5858@lakeread06...
> Hello All,
>
> Using Windows & Excel XP.
>
> I have a worksheet that has times located in every other column, A1:A30,
> C1:C30, E1:E30.  I then name the range "times".  I want to find the count
of
> times that are between 0:30:00 and 0:39:59 (30 and 39:59 minutes).  I
write
> the formula:
> =COUNTIF(times,">=" & TIME(0,30,0)) - COUNTIF(times,">=" & TIME(0,39,59))
> but get the error #VALUE!
>
> I have tried writing a formula for times in one column and consecutive
> columns and it gives the correct count, it is just when the times  in
every
> other column that the formula doesn't work.  I would apprecicate any help
in
> getting the formula to work for the times located in every other column.
>
> Thanks in advance,
> Michael
>
>


0
rumlas (268)
2/14/2005 7:05:08 PM
Thanks Bob, works great!!
Mike
"Bob Umlas" <rumlas@kpmg.com> wrote in message 
news:Osv3agsEFHA.4072@TK2MSFTNGP10.phx.gbl...
> Name the whole range "Times" (A1:E30), then use this formula:
> =SUMPRODUCT((Times>=TIME(0,30,0))*(Times<=TIME(0,39,59))*(MOD(COLUMN(Times),
> 2)=1))
> which will pick up every other column.
>
> Bob Umlas
> Excel MVP
>
> "Mike" <windme@cox.net> wrote in message
> news:x05Qd.63290$jn.5858@lakeread06...
>> Hello All,
>>
>> Using Windows & Excel XP.
>>
>> I have a worksheet that has times located in every other column, A1:A30,
>> C1:C30, E1:E30.  I then name the range "times".  I want to find the count
> of
>> times that are between 0:30:00 and 0:39:59 (30 and 39:59 minutes).  I
> write
>> the formula:
>> =COUNTIF(times,">=" & TIME(0,30,0)) - COUNTIF(times,">=" & TIME(0,39,59))
>> but get the error #VALUE!
>>
>> I have tried writing a formula for times in one column and consecutive
>> columns and it gives the correct count, it is just when the times  in
> every
>> other column that the formula doesn't work.  I would apprecicate any help
> in
>> getting the formula to work for the times located in every other column.
>>
>> Thanks in advance,
>> Michael
>>
>>
>
> 


0
windme (104)
2/14/2005 7:30:41 PM
Reply:

Similar Artilces:

I have published a worksheet to the web and it works good but one problem please help
Hello I published a worksheet to the web. The address is http://www.inforamp.com/~kidd/html/pmcshop1.htm The problem is I want to get rid of the scroll bars and the top men bar so ONLY the worksheet is present. Can someone tell me what I did wrong You help is greatly appricated Pau -- kid ----------------------------------------------------------------------- kidd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1579 View this thread: http://www.excelforum.com/showthread.php?threadid=27378 ...

Data conversion problem, thanks
double temp = 4232323232323.23432 how to convert to a String that just stores only 4232323232323 Thanks in advanced pcserver wrote: > double temp = 4232323232323.23432 > how to convert to a String that just stores only > > 4232323232323 > > Thanks in advanced double MyDouble = 4232323232323.23432; CString MyString; MyString.Format("%.0f", MyDouble); thanks ...

Creating Date Range in Pivot Microsoft Query
I have a pivot that I've connected to our SQL db using the MS Query. I'm having two problems with it: 1. In Access I create a date range to see dates between 3 and 15months ago with the SQL statement " WHERE (Trx.TrxDate) Between (Date()-485) AND (Date()-120)" - no problem in Access but not the MS Query program. When I do it through the Excel MS Query it says the Function 'date' is not valid. How do I describe the current date in the MS Query program? 2. In the same wizard I cannot combine two fields (in Access I would use Expr1: [Field1]&", "...

admin console cannot start, mapi problems
admin console cannot start, mapi problems. server 5.5sp4 working well, outlook session cannot run on server, same mapi problems Any error message in event log? -- Alvin "daniv" <anonymous@discussions.microsoft.com> ���g��l��s�D :10bef01c440b0$5525aa10$a001280a@phx.gbl... > admin console cannot start, mapi problems. > server 5.5sp4 working well, outlook session cannot run on > server, same mapi problems As I understand you installed Outlook on your Exchange Server, which is not a good idea. http://support.microsoft.com/default.aspx?scid=kb;en-us;313889 Dajo O...

Problem with tracking Database in Exch 2003
Hi, I have a installation of Exchange 2003 Standard, single store. When tracking emails with the tracking Database, most of the time I get the following message: The tracking database file *******.log is corrupted near byte offset xxxxx Miy server is in a fresh new RAID, everything is OK; no event log errors... What can be the reason? I suggest you delete and then recreate the log file to see if could fix this problem. -- Open ESM, expand servers -- Right-click on the server and then open its properties pane -- On the general tab, disable the message tracking temporarily, and th...

Range compariosn
I have multiple sets of ranges (10) and I would like to show the best of. Currently I have nested if statements to give me the "best of", but I was wondering if there was another/better way of doing this? Any Suggestions? This is what the ranges look like Group A rate 15 30 45 60 5 100 101 102 103 5.125 101 102 103 104 5.250 103 104 105 106 AVG(x) x x x x Group B rate 15 30 45 60 5 101 102 103 104 5.125 102 103 104 105 5.250 104 105 106 107 AVG(x) x x x x x = ...

Report filter problem
I have a command button called TodaysReport and an unbound textbox called txtDate whos defauld value is set to Date(). I need the on click event property of the button to filter the report only for the records whos dates are the same as that in the textbox. The name of field in the record source of the report is [ReceivedOn]. This works perfectly with any other data type, but not with the date/time data type. I get a blank report everytime. The format and input mask of the date in all three controls meaning on the report, record source and unbound textbox are the same. Why do I get a blank rep...

summing problem
Dear all, I have three columns with data: Col A Col B Col C 1 17 0 1 17 0 2 0 17 1 16 0 1 16 0 2 0 16 2 0 50 2 0 65 2 0 77 2 0 62 Col A always contains either 1 or 2. The amount of one's and two's in Col A can vary, as well as their order. In case of 1 in Col A the corresponding cell in Col B is nonzero and that in Col C is zero. In case of 2 in Col A, it is the other way around. Col D (results) should look as follows: Col D 0 0 51 0 0 48 50 65 77 62 Cell D3 is obtained by summing C3...

Transform range of cells into email addresses
I'have got a column of a sheet in Excel 2000 filled with email addresses but these are not recognized as email, despite they are in the right form (user@domain.xxx), I mean that they are not linked as an URL (mailto:.....) I think that this had occured because the sheet wasn't manually edited but it could be imported from a database. In fact, no sooner as I edit one with F2 and I press Enter, it becomes linked. Since they are about 5000 addresses, how can I manage to transform them automatically? Hy, William William, Assuming your email addresses are in column A, insert a new ...

Exchange 2003 and Win2003 problems with mailboxes
I installed Windows Server 2003 and Exchange 2003 2 weeks ago. After the install Internet email would not come through, I fixed that problem but after the fix there are 6 emails in the server that keep downloading into the mailboxes. you can delete all of the new emails that show up but they start downloading again and again. It seems as if the email never leaves the server. I have checked all of the Outlook setting and it is to remove the mail from the server (not leaving it on the server for X days). When I go into OWA you cannot delete the emails at all but they only download once. If...

Timesheet problem
Dear All, I would like to create a leave planner sheet which calculates my hours remaining. Problems occur when I enter the actual hours eg 7:24 (for a working day) is ok BUT entering 133:30 hours to start with gives me a date and an eroneous time! I have formatted the cell to [h]:mm but for some reason this gives me - 05/01/1900 13:02:00 as an entry. Beyond this I would like to incrementally reduce the balance when time off/leave is taken. Any help would be gratefully received. Cheers John North Yorkshire XP Pro Excel 2003 maybe this link helps: http://j-walk.com/ss/excel/userti...

back up problem
I can't figure out how to get Money 2002 to back up to the cd/rw drive. We just changed computers dell 5100 to a new dell 9100 and what used to work without any problem is now apparently impossible. I even tried routing it to the dvd/rw drive. I am the administrator on the computer and it keeps telling me to see the administrator that I don't have rights to save to that place (both the cd/rw and dvd/rw). HELP! I am computer challenged and I have hit the wall on what to do. Thanks. ...

Problem with Daylight to Standard time shift
Hi, I think there is a problem with the daylight to standard shift if you update the time (with SetSystem/LocalTime) after the shift has occured once. System: Samsung S3C2410, CE 5.0, SNTP client with DST. When looking at the time service DST code (dst.cpp) I see that the every time the time is updated an event is triggered and the time service checks the time zone information in the registry to determine if we are in daylight or standard time. For example, if the dst>std shift should happen at 3.00, when the time is 3.00 the clock goes back one hour to 2.00. Now, if the tim...

Problems with IDocHostUIHandler in Visual Studio 2005
My C++/MFC program (Visual Studio 6) displays an HTML page via CHtmlView. Clicks within the HTML page are notified to my C++ program by the IDocHostUIHandler and the function window.external. Usual proceeding, everthing works well. But when I compile my program with Visual Studio 2005 some items in the HTML page do not work any longer: • runtime error „’window.external’ is null or not an object“ through a click on a button • scrollbars appear again (actually suppressed by DOCHOSTUIFLAG_SCROLL_NO) • context menu (right mouse button) again activated These items are all associated with the...

problem with vc++ 6 appwizard
I am trying to learn mfc, but whenever I try to compile even an unmodified skeleton program under appwizard I get 20 errors from the afxdisp.h file. The errors have something to do with trying to do C linkage to the overloaded functions. I have no idea why it is trying to link in C, there are no 'extern "C"' commands associated with the errors in question. I reinstalled visual studio and upgraded to the latest sp of vc++ 6 just to be sure. I downloaded afxdisp.h from the microsoft website and compared it to the copy installed with visual studio, just to see if my file was s...

Problem with SelectNodes / SelectSingleNode
Hi We have the XML Below <Products> <Item Code="1"> <PN>Name</PN> <PC>ProductCode</PC> <MC>Manufacturer</MC> ...... </Item> </Products> I am trying to get the select nodes to work, basically I only want to select items where MC = 'Manufacturer'. The code below does not error but does not return any results, i am fearful that it is returning the child nodes of MC of which there are none. doc.Load(Server.MapPath(cstrXMLManuf)) Dim doc As New XmlDocument strXMLRequest = "Products/Item[./MC='M...

ListBox control over other controls paint problem
I'm putting a Listbox control over other controls in my form; but when i run the application and click on the controls that are partially hidden by the Listbox, these controls go on foreground and ruin the paint of the Listbox... How can i avoid this?? Thanks First say why do you want controls behind the listbox? Maybe you want to hide the controls behind the listbox instead? Ali R. "michele" <mandelbort@oggiweb.com> wrote in message news:LXzMb.245653$vO5.10164448@twister1.libero.it... > I'm putting a Listbox control over other controls in my form; but when i r...

Pub 2007 Serious Problem
I've used every Publisher from day one .... with 2007, the print window is slowing me down to no end. It is stupid in that when printing a business card, you have to do a layout each time to tell it where to print the 10 cards, then when you select the quality setting on your printer, and go back, you have to do it all over again. When you want to print 10 sheets you have to select 100 when the cards are to print 10 on a page. 2003 Pub never had this extra window and could print way faster. I'm trying to print 10 pages right now and it is taking about 10 minutes just to load...

Problem with Outlook pfbackup.exe add-in
I am trying to install pfbackup.exe on Outlook 2002.The program downloads normally and there are no error messages while running the installation program. However, after running the installation, the BACKUP command does not appear on the File menue as it should. I have rebooted and tried uninstalling and reinstalling the program but still do not get the BACKUP command. This program has installed flawlessly on my other computers and I cant understand why I'm having a problem with this one. By the way, NAV is turned off during the install. ...

Routing problem in exch 5.5
Hello, we're currently testing the routing objects on our exchange 5.5 server, we've managed to install the routing wizard to create a simple approval process on items in a particular folder. The routing wizard completes succesfully and tells us that the route has been installed. When we look in the agents tab of the folder we can see the routing agent is installed. But when we post an item to this folder nothing happens! What could be the cause of this routing functionality not working? Kind regards, Tom Anybody got an idea? regards, Tom "Tom B" wrote: > Hello...

Cells.copy problem
Hi I have strange problem with my VBA code where I use sheet("name").select cells.copy I use this code in many other workbooks without any problems but today I have problem with new workbook I created. While the code is executing I recive the error: Select method failed or another error: object oriented error ? does anywone know what is the problem ? regards Peter Piotr, Dzien dobre. maybe you have some merged cells... this makes copy-ing more difficult. grtx's Martijn "Piotr" <hokah@wp.pl> schreef in bericht news:1133785764.499934.64550@g49g2000cwa.google...

Problem confrim meeting
hallo i work in tha office 2003 sp3 (the server is exchange 2003 ) When I try to confirm meeting summons I accept errore "Organizer of the meeting you should not comment on the meeting" The message appears in various meetings but i not organizer the meeting . what i do ? thanks והמשך/י, נא, לקרוא את השורה הבאה: **************** אם תגובתי עזרה לחץ/י, נא, על ''''כן'''' בפס האופקי התחתון! ...

Calander Problem #3
Hi When I try to invite someone to a meeting I can that he is busy or not, but our problem is that insted of seeing that the person is buy we can see in what he is busy? How can we stop it, so that we be able only to that he is busy? Than'x S ...

Problem with auto correction in cells
When I type ex. 0771 in a cell, excel auto change it to 771. How do I avoid this -- Message posted from http://www.ExcelForum.com Hi if you need a leading zero you have some options: 1. If you don't need to calculate with these values either preformat the cell as 'Text' or enter the value with a leading apostrophe 2. If you need these values as numbers you may apply a custom format to these cells. In your case for example the custom format 0000 >-----Original Message----- >When I type ex. 0771 in a cell, excel auto change it to 771. >How do I avoid this? > &g...

Problem with installation of Outlook client
Hello, after installing Outlook client, I ve tried to configure the firewall by adding an exception a, the problem that I don't find the program "Microsoft.Crm.Application.Hoster.exe" in my computer. As consequence , a CRM Server connection error is raised when I am opening the outlook. Some help Thanks in advance ...