counting cells #4

What fromula do I use if I want a cell to count how many times a number 
(time) occurs in a range of cells.
e.g. cells     a1                 a2                  a3                    
a4                    a5
              0700-1600    0700-1200      1200-1600        0800-1100        
1100-1500
If I wanted the formula to look for how many times 0700 and 0800 occured, 
the answer would be 3.
Thanks
in advance 
Jason
0
Boenerge (52)
12/17/2007 10:11:39 PM
excel 39879 articles. 2 followers. Follow

2 Replies
601 Views

Similar Articles

[PageSpeed] 3

This counts the 0700:
=SUMPRODUCT(--(LEFT(A1:A5,4)="0700"))
This counts 00700 and 0800
=SUMPRODUCT(--(LEFT(A1:A5,4)={"0700","0800"}))
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Boenerge" <Boenerge@discussions.microsoft.com> wrote in message 
news:440D108F-B9D5-4CA7-A8AC-8B86E0AF534F@microsoft.com...
> What fromula do I use if I want a cell to count how many times a number
> (time) occurs in a range of cells.
> e.g. cells     a1                 a2                  a3
> a4                    a5
>              0700-1600    0700-1200      1200-1600        0800-1100
> 1100-1500
> If I wanted the formula to look for how many times 0700 and 0800 occured,
> the answer would be 3.
> Thanks
> in advance
> Jason 


0
bliengme5824 (3040)
12/17/2007 11:27:56 PM
Try this:

For 0700:
=COUNTIF(A1:E1,"0700*")

For 0800:
=COUNTIF(A1:E1,"0800*")

For both:
=SUM(COUNTIF(A1:E1,{"0700*","0800*"}))

Those formula return 2, 1, and 3, respectively.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Boenerge" <Boenerge@discussions.microsoft.com> wrote in message
news:440D108F-B9D5-4CA7-A8AC-8B86E0AF534F@microsoft.com...
> What fromula do I use if I want a cell to count how many times a number
> (time) occurs in a range of cells.
> e.g. cells     a1                 a2                  a3
> a4                    a5
>              0700-1600    0700-1200      1200-1600        0800-1100
> 1100-1500
> If I wanted the formula to look for how many times 0700 and 0800 occured,
> the answer would be 3.
> Thanks
> in advance
> Jason



0
12/17/2007 11:51:21 PM
Reply:

Similar Artilces:

Linking many cells
I wish to link many cells in (e.g. A1:40) in sheet 1 to A1:A40 in sheet 2 without doing it one by one so that whaever I type in A1 sheet 1 appears in A1 sheet 2. Please any idea? dk As long as your 'from' and 'to' blocks are structured identically (eg 1 column of forty rows in your example), it's straightforward. Click in the first 'to' cell, Sheet1!A1. Type the '=' sign, then click in the first 'from' cell, Sheet2!A1, then hit enter. Your formula in Sheet!A1 should read =Sheet2!A1 (look for it in the formula bar). Grab the fill handle (th...

Naming Ranges #4
Hi, When I name a Range on Sheet1, then make a Copy of Sheet1 to say Sheet2, then access the Define Names dialogbox, I can see the named Range listed twice. One is noted as belonging specifically to Sheet2 and the other appears without a sheet reference. When assiging a name to a Range, can I specify that it belongs to a certain sheet? Also, how would I change this code to assign the sheet name aswell? ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:="=Sheet1!R1C1:R10C1" I am trying to assign a the name 'Column_Header_Range' to each sheet in my work...

Cell Reference 01-12-10
What I am looking for is that when I enter a formula (In cell B1) to pick up value in A1. Now I need value from cell A5 in cell B2. Next value I need in B3 is A9. Everytime I have to change the cell values manually in column B. Formula I use : - =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) Now I need value from cell H336 and I manually change H331 to H336 shown below =IF(OR(AK64<$D$4,AK64=$D$4),H336,0) Any way to make this automated. Thanks Ankur Bhateja ankur.bhateja@hotmail.com Instead of =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) you could say =IF(AK64<=$D$4,H331,0) ...

Auto change formula in cells when source is changed
Good day experts; If specific cells have the same formula for example: C1 = Product( A1;B1) C16 = Product(A16;B16) C17= Product(A17;B17) And so on ... What formula should i write in C16, C17, ... so that: when the formula in the source cell, C1, is changed the formulas in C16, C17, ... would automatically change accordingly. I tried "=C1" it does not work Thanks in advance Carlo carlo wrote: > Good day experts; > > If specific cells have the same formula for example: > C1 = Product( A1;B1) > C16 = Product(A16;B16) > C17= Product(A17;B17) > And so on ... ...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

why do i get #### in my formula cell?
I don't really know much about Excel, and my formula (it's just simple arithmetic formulas) returns with ##### displayed on the cell, with a tooltip showing the cell's correct value when I hover over it. airn, make the column wider and see if that helps -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "airn" <airn@discussions.microsoft.com> wrote in message news:C6F4DD7C-3D7D-475C-894D-0F7745735666@microso...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

Count column difference
Hi Using MSExcel 97. I have two columns of data e.g. A1: A4, containing values 5,10, 3, 6 B1:B4, containing values 3, 8, 7, 4 I wish to perform a count (e.g. in C5) of the number of rows where the value in column A exceeds the respective value in column B (in this case count = 3, as A1>B1, A2>B2, and A4>B4). Just cannot get my formula right. Tried using an array (but difficult when comparing the difference between two columns), and COUNT. Thanks in advance for any suggestions. Wizzy ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.co...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

How to use excel file created in version earlier than 4.0a in 4.0
Thanks to whomever answered my query but when I opened the email the screen was blank. My laptop is sick. My question was and still is, how can I use my Excel files created in a recent version on my laptop in my PC which is loaded only with Excel 4.0a . Please and thanks. -- Thanks, Ajimmo Hi Ajimmo You can't, the file format changes twice since version 4. You need a newer Excel version, a compatible spreadsheet program, or simply a viewer if all you want to do is view / print the file. HTH. Best wishes Harald "ajimmo" <ajimmo@discussions.microsoft.com> skrev i m...

CRM 4 Mobile?
I am wondering what options are planned for working with CRM 4.0 on Windows Mobile devices. All my searches so far seem to end with Mobile Express for CRM 3.0 and the Mobile Client for 3.0, and bad links pointing to the old Sandbox on the GotDotNet site. Any information is greatly appreciated. No information at this time ======================= John O'Donnell Microsoft Dynamics ISV Architect Evangelist http://blogs.msdn.com/usisvde "Brett" <Brett@discussions.microsoft.com> wrote in message news:83F08067-EF7F-4190-86F0-019342E06642@microsoft.com... >I am wonder...

date #4
Hi All I live in South Africa, and I have an interesting problem that persists in Excel 2003 and 2007. =TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006 =month(now()) returns 12 =text(month(now()),"MMMM") returns January =year(now()) returns 2006 =text(year(now()),"yyyy") returns 1905 Why would this be? Thanks Chris Chris, =text(month(now()),"MMMM") should be =text(now(),"MMMM") and =text(year(now()),"yyyy") should be =text(now(),"yyyy") or just =year(now()) Otherwise, you are calcing the month of the 12th day afte...

How to show the month of the referenced cell (containing a date)
If a cell holds the date "2/1/08", what formula can I use that will give me the following result: "Feb - 08" I tried =Month(a2)......but I just get the number of the month. Thanks. =TEXT(A2,"mmm - yy") Or simply =A2 with the cell custom formatted mmm - yy HTH. Best wishes Harald "Dave K" <fred.sheriff@gmail.com> skrev i melding news:165b03da-19ba-40b8-b9fe-77b17ef045b5@d45g2000hsc.googlegroups.com... > If a cell holds the date "2/1/08", what formula can I use that will > give me the following result: > > "Feb - 08&...

blank postcard template 4 x 6
Hi, I have a postcard that I made in MS Publisher that is 8 1/2 x 5 1/2 in size. Obviously I can't use a .23 cent stamp on this item. I was interested in modifying my larger postercard and reducing it to a 4 x 6 template size so I can save on postal expenses. I couldn't find a blank template! I've purchased card stock and am ready to figure out a way to convert the larger size to (4) 4x6 smaller postcards printed on a full sheet 8 1/2 x 11. I'll then have them cut to size. So.. is there a easy to use template out there and how do I convert (shrink) my current postc...

Conditional Formating: linking to display another cell
Hello, I am trying to make a traffic light with symbols and I've read to 'use a separate cell for the dropdown choices, with their resulting value linked into the formatted cell through an IF function, using the character that you want to display.' So: =IF someone enters '1' in B8, THEN display contents of $C$4 (will it display font and attributes?) =IF someone enters '2' in B8, THEN display contents of $C$5 =IF someone enters '3' in B8, THEN display contents of $C$6 But I've been reading everywhere and CF is very new to me and I need ...

Cell with email address
I have a cell where I just want to record an email address. However, whenever I enter it tries to connect to Outlook, thinking I want to send an email. How can I stop this? -- Les Les It shouldn't try to connect unless you click on it but you can precede the entry with a single apostrophe or press Ctrl+Z after entry (undo) and a second entry should stop it -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Les" <Les@discussions.microsoft.com> wrote in message news:9B53EE8A-A0A8-4AFD-A09F-116DDE063747@microsoft.co...

can I make cell "yes" equal 1, "no" equal 0
in another cell? =IF(AND(A1<>"yes",A1<>"no"),"",(A1="yes")*1+(A1="no")*0) -- HTH RP (remove nothere from the email address if mailing direct) "can I make cell yes equal 1, no equa" <can I make cell "yes" equal 1, "no" equa@discussions.microsoft.com> wrote in message news:8E222344-E390-4D39-B82D-39586CC55A64@microsoft.com... > Picky but I don't think that you need the " +(A1="no")*0 " Just =IF(AND(A1<>"yes",A1<>"no"),"&quo...

unable to send email #4
I am receiving error message, "A TCP/IP error occurred while trying send data to the server. (Error number 0x800ccc13). Can anyone provide any assistance to resolve this issue/ Thank you so much! Hi, Could you be more specific about the account type and the version ofOutlook etc ...??? Hope this helps !!! With Regards, Sudharson.AN "Moises" <anonymous@discussions.microsoft.com> wrote in message news:537701c3ffd4$8fac4fd0$a101280a@phx.gbl... > I am receiving error message, "A TCP/IP error occurred > while trying send data to the server. (Error number...

Outlook Vs Outlook Express #4
I am a longtime user of MS Office Suite. For my mail program I use Outlook bundled with MS Office 2002. Recently I purchased and installed McAfee's Firewall, Internet Security and Virus bundle. I have set and reset nearly all of the security settings to what I thought was an acceptable level. The first issue manifested itself by error messages indicating failure to recognize server settings etc. I called Roadrunner support and was imediately moved to Outlook Express because they do not support Outlook. Although I had to re-enter all of my contacts this worked for awhile. Now, ho...

Restoring Business Portal 4.0 and SharePoint Services 3.0
We are in the middle of trying to restore Business Portal 4.0 and SharePoint Services 3.0 from one environment to another( Dev to TEST). This includes GP data as well. The Business Portal Dev server points to a GP dev server and the I am wondering if anyone else does this? I think we have it pretty much solved. But there a couple things still not working quite right. Any insight would be appreciated. Thanks Scott, Thanks for using the newsgroups. What you're doing is quite common. Customers often set up Business Portal in a test environment and verify prior to going live. Int...

MSCRM 4.0 ISV Custom buttons not visible in Contact Detail Toolbar
Hello, After enabline isv integration in 4.0, I can see in the Accounts detail level toolbar the sample isv buttons, but when I goto Contacts detail toolbar, none are visible. I just added a button to the detail level toolbar in CRM 4.0. It was added basically to the same place as my old 3.0 in the isv.config, but after adding it wasn't visible. I then went to the ReadyServer image from Microsoft, and noticed that the isv.config was not present! (isv.xsd was present and accounted for). Since I could see the isv buttons in the account entity detail toolbar, I added my custom butto...

MS Money 2002 #4
How do I change my email address when I log onto MS Money 2002? You mean the mail address on your Passport? You have to go to the Passport Web site to do that. http://www.passport.com. Were I you, to be on the safe side, I'd go to File|Login Lockbox and remove the passport from your Money file first. Then go and change the email address on the Passport, the re-add the Passport, if you really want to. "Dawna" <anonymous@discussions.microsoft.com> wrote in message news:4caa01c3e46f$c54f09a0$a401280a@phx.gbl... > How do I change my email address when I log onto MS Money ...

Identifying LOCKED cells
Is there a way to identify locked cells, other than going through the processof looking at each cell's format? Here's by quandry. I am working a worksheet which has both locked and unlocked cells. Unfortunately some of the locked cells should be unlocked so that data can be input. Thanks for your help To reply to this message, remove "mand" from my address. Thanks I've dragged the lock button onto a toolbar. When I click on a cell, I can glance at that and see if it's depressed (not suicidal!) or not. Tools|Customize|Commands Tab|Format category. Look for "Lo...

Find a value in cells
Hi, all, I have something like this : a 1 b 4 c 5 d 3 I'm doing a max function in the number column and it returns me '5' But, after that, I want to know the letter which corresponds to the max number How can I do that? Thanks Nic -- nicgendron ------------------------------------------------------------------------ nicgendron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25151 View this thread: http://www.excelforum.com/showthread.php?threadid=386417 Let's say that...

Public Folder Question #4
Is there a way to enumerate all folders and posts (including attachment labels where applicable) and redirect to a text file or CSV in a similar fashion that the DOS tree /F /A command would work? Thanks! ...