Count If Formula #4

I need a formula that will figure the following:

If column C = 3, count all times < 00:45
If column C = 4, count all times < 00:45
If column C = 3, count all times > 00:46

Thanks.
0
Denise1 (113)
10/14/2005 5:01:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
458 Views

Similar Articles

[PageSpeed] 40

Your description is a bit confused.You have different actions for the same
value (C=3). And where are the times, in a different column than C?

So, making many assumptions, perhaps

=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C200,{3,4},0))),--(D1:D200<TIME(0,45,0)))+S
UMPRODUCT(--(C1:C200=5),--(D1:D200<TIME(0,46,0)))


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Denise" <Denise@discussions.microsoft.com> wrote in message
news:7F3DFCD4-4FCC-493B-9B0D-C624A5BDD352@microsoft.com...
> I need a formula that will figure the following:
>
> If column C = 3, count all times < 00:45
> If column C = 4, count all times < 00:45
> If column C = 3, count all times > 00:46
>
> Thanks.


0
bob.phillips1 (6510)
10/14/2005 5:23:28 PM
Denise,

I believe an array formula may be the answer you're after.

First, let me ask if you mean't...

If column C = 3, count all times < 00:45
If column C = 4, count all times < 00:45
If column C = *5*, count all times > 00:46

(You had C = 3 twice, which didn't make sense to me.)

If that is what you meant, I believe this will do what you ask:

{=SUM(IF(C1=3,IF(F3:F16>45/24/60,1,0),IF(C1=4,IF(F3:F16<45/24/60,1,0),IF(C1=5,IF(F3:F16>46/24/60,1,0)))))}

Where C1 is the column C value you name, and F3:F16 is the range of all
times, that are formatted as such 0:40, where the actual value (up in
the formula bar) is 12:40:00 AM.

The "...45/24/60..." parts of the formula help convert time to decimal
values.

To input an array formula (to get those "{" and "}" around it, you
simply type in the formula without them, then hold SHIFT+CNTRL when you
hit ENTER.

Let me know if you need help.


-- 
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26423
View this thread: http://www.excelforum.com/showthread.php?threadid=476304

0
10/14/2005 5:44:06 PM
Reply:

Similar Artilces:

Sort affects formula
I use Excel to manage records (rows) of data. Data (a cell) in any given row uses a formula which makes use of data from a different row. The rows may or may not be sequential. When I sort the rows the formulas, since the rows change order, they no longer point to the correct data (cell). It there a straight forward way of accomplishing this? Thanks. When you sort the data, are you sorting the formulas with the data? Give us an example of data, and an example of the formulas that were right, and now wrong. -- ** John C ** "Its-Just-Me" wrote: > I use Excel to manage record...

Formula of another formula.
Hi. I often have problems calculating an answer using a formula that has to reference another formula. Why is this?? My current problem is, I have a conditional statement saying if cell a1 (for example) is <=35, then 3, <=44, then 4, etc. Cell a1 has a formula that grabs data from another sheet. When it grabs 35 for example it is ignoring the = of the <= and gives me 4, it should be 3. How come the <= is interpreted as just < and why do I always have problems referencing other formulas in current formulas? Thanks! -- Thanks! Stephen Stephen, It's likely to be cas...

Blanking cells with formula errors
The cell with the below formula returns the #N/A sign. =VLOOKUP(A3,'!MW'!A:J,5,FALSE) The value I am looking up (A3 on this occasion) is sometimes #N/A (which I know) Is there an IF formula that I can incoroporate within the above formula to return "" if my lookup value (A3) equals #N/A. ???? I hope someone follows this! Thanks in advance! =IF(ISNUMBER(MATCH(A3,'!MW'!A:A,0)),VLOOKUP(A3,'!MW'!A:J,5,FALSE),"") -- Regards, Peo Sjoblom "Richard Layzell" <anonymous@discussions.microsoft.com> wrote in message news:099101c3a7...

Mapping disabled users in CRM 4.0 Import Organization
Hello, I am in the process of moving a CRM deployment to new servers in the same AD domain. I am using the Import Organization tool in Deployment Manager. When I get to the Select Method for Mapping Users step, I select Keep Existing Users since the new deployment will be on the same AD domain as the old one. When I hit Next, I get an error message - "There was an error during the users mapping." When I hit OK on the error message, I am taken to the Edit Users Mappings page and notice that all of the unmapped users are disabled users in CRM who have been deleted in AD. I...

Excel should let me do absolute copies of cell formulas
There are two types of cell copying that MS Excel does not currently provide for: 1) Copying cell formulae from one region into another of the same size without changing them 2) Copying cell formulae from one region into another of the same size without changing them if they reference cells outside that region, but treating them as relative if they are inside the copied region. These would be very useful and would help to cut down on errors made by copying cells. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To...

Array formula help #2
Currently I have Individual Options Name =SUMIF(Options!$A$8:$A$134,B4,Options!$B$8:$B$134) which works fine. I am trying to add an extra detail to a summary sheet for the exercise price of options, so the sheet would look like: Name Price Price Price Total I am not sure why this array formula keeps returning #VALUE# for the price breakout =SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134=C3)*Options!$B$8:$B$134) or =SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134=C3)*(Options!$B$8:$B$134)) Detail spreadsheet = "Options" (Options!$A$8:$A$134=B4) A8:A134 is the transaction ...

having total of formula automatically change
HI - hoping to get some assistance here - We have a spreadsheet that has the following: Column 1 - Cost (Dealer Cost) Coumn 2 - Mark up (using : =SUM(C7*0.05) ) Column 3 - Selling cost (=SUM(C7:D7) ) What we need to have happen at this point is Colum 4 - Automatically rounds the amount to *.95 cost and then hav it refigure the %of markup in Column 2 SO: IF we have : $42.95 $2.15 45.10 Need this to reformulate 45.10 to 44.95 an then refigure $2.15 mark up cost to the correct % -- Message posted from http://www.ExcelForum.com If I understand you correctly: rounded ...

New Help update for Microsoft Dynamics CRM 3.0 and 4.0
We have updated Help based on answers to questions and feedback from customers. The latest version of Microsoft Dynamics CRM 3.0 Help is now available as part of Update Rollup 3. This is Help update 3.4. http://www.microsoft.com/downloads/details.aspx?FamilyID=d5b1fbbe-dfc2-4a33-b5fd-cae99e8e9dab&displaylang=en We have also updated the Help for 4.0. This is Help update 4.1 and is a stand alone download. http://www.microsoft.com/downloads/details.aspx?FamilyID=31db715d-eb10-4fe9-9762-3c768011dfdb&displaylang=en ...

In a given cell, how can I count how many carragereturn/line feeds are imbedded?
If I have a cell (A3) and want to see how many line feeds are in it, how do I caunt that? How abount any other charater if it is not the same? Thanks Phil I figured it out! It is =(LEN(A3)-LEN(SUBSTITUTE(UPPER(A3),CHAR(10),"")))/LEN(CHAR(10)) Thanks though! Phil "Phillips" <Phillips*NOSPAM*@RecycledReefs.com> wrote in message news:ek8ub.174775$ao4.578644@attbi_s51... > If I have a cell (A3) and want to see how many line feeds are in it, how do > I caunt that? How abount any other charater if it is not the same? > > Thanks > Phil > > See...

Outlook 2000 SP3 #4
I'm running Outlook 2000 SP3. I have a 3rd party application sending out emails. Everytime an email is set to go out, I am prompted to send it or not. It is worried that it may be a virus. I did not have this problem with SP1. Is there a way to turn this feature off? Thanks. Outlook 2002 SP3 adds additional properties to the list of those that are affected by the security features. As a result, one of your add-ins needs updated to the latest version - antispam add-ins are a common cause although others are affected by the changes as well. Many add-ins were updated following...

Formula #38
On excel how do I add 25 to my previous product For example: 175 200 250 325 I want a formula to start with 175 then add 25=200 then I want it to take 200 and add 50, 25+25 250 then take 250 and add 75, 25+25+25 325 How do I do this using a formula? in A1, put in 175 in A2 put in =A1+25 then drag fill down the column. -- Regards, Tom Ogilvy "Dianna" <princess_di_69145@yahoo.com> wrote in message news:d4704b7e.0411150718.5a510670@posting.google.com... > On excel how do I add 25 to my previous product > For example: 175 200 250 325 > I want a formula to star...

Auto-Numbering for Cutom entity CRM 4.0
Hello, This could be a toughy but I hope someone can help. I have custom entity which I need to autonumber a job number in the following way:- 09-215-001 09 being the year, 215 is the account number, 001 is the job number then when the next is created for that customer it will be 09-251-002 09-251-003 etc.... Any help much appreciated You should implement this type of functionality using a pre-create plugin. Depending on how much development skills you have this should not to difficult. -- Patrick Verbeeten (MCPD) CRM/.NET Consultant Aviva IT Tools for CRM Developers and Ad...

Formulas not updating until file is saved
I'm using Excel 2003 11.5612.5606. I recreated one of our workbooks that has various formulas in it. Some of which are just something like =A1 where A1 contains the customer name. The customer name doesn't show up in that cell until after the file is saved. What did I do wrong? -- Tech ------------------------------------------------------------------------ Tech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15107 View this thread: http://www.excelforum.com/showthread.php?threadid=492762 The file is probably set for manual recalculation - you c...

Applying a formula to a cell
I would like to set up a button in a spreadsheet that adds 2 years to the value of of the currently selected cell containing a date. the forumla would look something like this: =DATE(YEAR(A1)+2,MONTH(A2),DAY(A3)) but is there a way I can have this formula applied to a cell when I press a button? I would like the cell to contain the result, rather than the formula. Thanks guys. On Aug 14, 12:02 pm, Tom Roberts <TomRobe...@discussions.microsoft.com> wrote: > I would like to set up a button in a spreadsheet that adds 2 years to the > value of of the currently selected cell contai...

Formula problem
I have "inherited" a spreadsheet from someone else and am having problem creating new formulas (there currently are no formulas in th spreadsheet). I don't know if there is an option setting causing th problem or if it is something else. When I enter a formula, such as =IF(A1=1,"TRUE","FALSE"), it shows th formula in the cell instead of the result. I have tried a couple o things to figure out what's going on without much success. If I enter the exact same formula in a new workbook, it behaves as expect it to. If I enter the exact same formula in a ...

mail flow count
Hi everybody, Are you guys aware of a tool that counts all the incoming/outgoing e-mails for an Exchange organization? It will probably count all the e-mails from the transition logs or from the Message Tracking logs. Thanks, Sorin The products usually recommended are Promodag Reports and Quest MessageStats, although if you want to do something for free, try LogParser (www.logparser.com) -- Neil Hobson Exchange MVP http://www.msexchange.org/Neil_Hobson/ http://www.msexchangeblog.com "SorinD" <SorinD@discussions.microsoft.com> wrote in message news:75FF3408-E4FA-4E...

Copying Formulas
Hi All, This isn't a programming issue but a Formula issue. I want to copy a group of formulas from 1 workbook to another. When I do, it references the prior workbook. Is there a way to paste a group of formulas but have them refence the sheets & cells of the current workbook? I tried paste special -> Formulas Thanks chad You can re-direct links after the fact using Edit>Links and changing the source workbook to current one. Or you can edit before copying. Edit>Replace What: = With: ^^^ Replace all. Copy to new workbook then rever...

protect formulas
Private Sub Workbook_Open() Worksheets(1).EnableAutoFilter = True Worksheets(1).EnableOutlining = True Worksheets(1).Protect UserInterfaceOnly:=True End Sub Hi! using this code I protect only sheet1, but I need protect both sheet1 and sheet2. Does anybody knows how to? Thanks in advance Osmario. Private Sub Workbook_Open() Worksheets(1).EnableAutoFilter = True Worksheets(1).EnableOutlining = True Worksheets(1).Protect UserInterfaceOnly:=True Worksheets(2).EnableAutoFilter = True Worksheets(2).EnableOutlining = True Worksheets(2).Protect UserInterfaceOnly:=True End Sub or even: ...

Count of new emails is wrong in Outlook shortcuts bar
Hello, I've got Outlook 2002 SP3 and have the Shortcut bar visible on the left side of the screen. The inbox says there are 6 new messages when in fact there are only 2 new messages. If I go in and mark those 2 as read, the Shortcut bar says there are 4 when in fact there are none. Is there a way to fix this so it displays the correct number? It was working fine up until yesterday and I've had this computer with this version installed for over a year. Thanks for the help. Mark Try scrolling all through your Inbox - happened to us when 'someone' sent us emails from a ...

How do I stop Excel auto formatting the text 3-4 as 04 Apr?
I run a web query that imports soccer results to Excel. Unfortunately the web site formats the result as 3-1 for example. This text is automatically converted to 03 Jan by Excel. I can't seem to stop this happening, even if I explicitly set the cell format to text. Doing so just converts the contents, which were a date, to the internal date number and if I re load the data it reverts to 03 Jan again. Does anyone know how I might get excel to leave the format of the cells alone? thanks ahughf There is no turn off for this, you can either preformat the cells as text or precede the e...

money: 4 Millions Domains data with Category
Successfull Internet and Direct Marketing products on www.promotionsite.net * NEW * DOMUS Domains Toolkit Fall 2004 - Unique on the Net 4 Millions "Whois" Domains data with Expiration Date and Category*. Ultimate Version (October 2004) - Our best rate starts from US *$149*. A wonderful tool for Internet and Direct Marketing. Available in Basic, Advanced and Full Editions. It contains a domain database with 4 millions *FRESH* October 2004 New records .com, .net, .org. 4 Gigabytes MS Excel data zipped on CD-Roms/Dvd. Compression 3:1. MS Excel or Text tab del...

format cell formula
I am trying to get this to work... IF C8 = 1 or 2 or 3 or 4 then print 200 in cell M7 IF C8 = 5 or 6 then print 250 in cell M7 I have tried various ways (hopeless at formulas) but can`t get it to work for me. help appreciated thanks Hi, Juco; I didn't see anything in your actual question to do with formatting, but here is one answer to the question: use the IF function with the OR function for its first argument, 200 for its second, and another IF function for its third. Use another OR function for the first argument in the second IF function, 250 for the second, and "" f...

Data Entry and Formulas
Good afternoon folks, I'll try to keep this short. The formula i'm working with =-sum(c6:h6)+a6, is in cell b6. ? how can I enter a new value in a6 and have b6 display the total of both the existing b6 and new entry in a6. I'm sure I'll have many more ?'s but I'll start with this one first. To any one who writes program formulas for a living lets talk. You can do what you want but anything based on accumulating in a cell is very error-prone and next to impossible to trouble-shoot because there is no "paper trail" to look back at. But see John McGimpsey'...

formula/cutting cells problems.
Apologies if this is the wrong forum or it's not descriptive enough o what have you, but anyway, let's see if anyone can tell me what to d here. So I've got a series of numbers in cells B1-K1 and cell L1 is set t take the average of these cells. That's all fine and good. Now, I wan to move the data in B1-J1 over to C1-K1 and put a new figure in B1 whilst L1 gives me a new average but when I move the data, L1 no changes, wanting to take the average of C1-K1. Suffice to say I don' want it to change. Sure, I could change it back, but I have 1200 row to do and not all of them ...

can I use 2 formulas in 1 cell and display both results
Hi there, I was wondering if its possible to have 2 formulas in one cell. Such as vlookup one value and then vlookup another value and display both results in one cell. Thanks You cane use: =VLOOKUP(...)&"-"&VLOOKUP(...) HTH -- AP "chris" <chris@discussions.microsoft.com> a �crit dans le message de news:30B112DA-D57C-4498-BBB0-CF75F883F6BC@microsoft.com... > Hi there, > > I was wondering if its possible to have 2 formulas in one cell. > > Such as vlookup one value and then vlookup another value and display both > results in one cell. &...