Counting how many times a number appears in a list?(EXCEL)

hi, got a small problem in excel, situation as below:

grade
1
2
3
1
2
1
2
3
4
1
1

what formula do I use to count how many times (e.g) 1 appears in th
list? I thought I could do it with the Count function but haven
managed to.

Cheers

--
Message posted from http://www.ExcelForum.com

0
4/21/2004 6:37:45 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
534 Views

Similar Articles

[PageSpeed] 17

The "countif" formula should do!

Dunca

--
Message posted from http://www.ExcelForum.com

0
4/21/2004 6:56:44 PM
=COUNTIF(A1:A15,1)

-- 

Regards,

Peo Sjoblom


"sox >" <<sox.152stn@excelforum-nospam.com> wrote in message
news:sox.152stn@excelforum-nospam.com...
> hi, got a small problem in excel, situation as below:
>
> grade
> 1
> 2
> 3
> 1
> 2
> 1
> 2
> 3
> 4
> 1
> 1
>
> what formula do I use to count how many times (e.g) 1 appears in the
> list? I thought I could do it with the Count function but havent
> managed to.
>
> Cheers!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre08 (1112)
4/21/2004 6:56:55 PM
sox, =COUNTIF(A2:A12,1). Substitute your range.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

"sox >" <<sox.152stn@excelforum-nospam.com> wrote in message
news:sox.152stn@excelforum-nospam.com...
> hi, got a small problem in excel, situation as below:
>
> grade
> 1
> 2
> 3
> 1
> 2
> 1
> 2
> 3
> 4
> 1
> 1
>
> what formula do I use to count how many times (e.g) 1 appears in the
> list? I thought I could do it with the Count function but havent
> managed to.
>
> Cheers!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
DDMara (266)
4/21/2004 7:01:10 PM
Here's a neat formula  ( as a check against your original request)
=SUM(1/COUNTIF(A3:A12,A3:A12))<<Chg Range to your Range
which will give you the number of unique numbers in your range.
must be array-entered by not using just Enter, but rather
Control+Shift+Enter
Doing so will place the {  }  characters around.  << Such characters CANNOT
be
entered at the keyboard..
HTH
JMay

"sox >" <<sox.152stn@excelforum-nospam.com> wrote in message
news:sox.152stn@excelforum-nospam.com...
> hi, got a small problem in excel, situation as below:
>
> grade
> 1
> 2
> 3
> 1
> 2
> 1
> 2
> 3
> 4
> 1
> 1
>
> what formula do I use to count how many times (e.g) 1 appears in the
> list? I thought I could do it with the Count function but havent
> managed to.
>
> Cheers!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
jmay (696)
4/22/2004 12:14:58 PM
Reply:

Similar Artilces:

Downloading Bank Transactions Multiple Times (MSM2003)
If you download transactions from your bank (or whatever) multiple times -- say downloading all transactions several times a month -- will Money (2003 in my case) recognize that this has been done and ignore the duplicates, or will it just require matching and/or reconcilliation each time. I'd like to keep my Money records up-to-date (i.e., not wait to the end of the month) without having to keep track of which transactions have or have not been downloaded. Currently, I download to a file all transactions for a given statement cycle each month on or after the statement closing date. This...

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

Removiing decimals from an excel number
Here is the issue: I have numbers like this one - 10000.01.01.200 I just want the prime number 10000. How can I do that? I tried Ctrl+F and replace but is there a more efficient way? Thanks! =LEFT(A1,FIND(".",A1)-1) -- Gary''s Student - gsnu201001 "Data Analyst" wrote: > Here is the issue: I have numbers like this one - 10000.01.01.200 > > I just want the prime number 10000. How can I do that? I tried Ctrl+F and > replace but is there a more efficient way? > > Thanks! try this =LEFT(A1,FIND(".",A1,1)-1)...

Is it possible to setup a self-updating master list?
I want to setup a master data spread sheet that will update automatically from data entered in muliple other sheets. Bassically if I enter data additional data into the existing sheets that data would automatically appear in the Master data sheet. Does Anyone have any answers? mbh: You might try using either VLOOKUP formulas or linking the main tab with the others. Could you be more specific or post an example spreadsheet with what you are trying to do? -- cparaske ------------------------------------------------------------------------ cparaske's Profile: http://www.excelforum...

Converting Excel to Space delimited
Hi, I have a spreadsheet that I am trying to convert to a space delimite file, so that I can load it into a DB2 database. The only way I can find to do this is to save the file as a spac delimited *.prn file. However, for some reason Excel inserts a lin break after every 241 characters. I need to find a way of exportin long lines (up to around 1000 characters, spread across severa columns) to a space delimited file without Excel inserting a line brea until the end of the row. Has anyone found a way around this? Thanks for any help -- mv586 ------------------------------------------...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

Exporting Excel data to Photoshop or similar?
Hi My friend is an amateur archaeologist. He has made a geophysic sampling device, and he can download the raw data into an Exce spreadsheet. Now he would like to visualise the data. The Excel chart features ar inadequate, so he wants to export the Excel data into (plugin-enhanced) copy of Photoshop or something similar. This wil allow him to do contrast enhancement, edge detection, gaussia smoothing, etc. The fundamental question is - what is the best way to get the data ou of Excel and into Photoshop, or a similar program? TIA Pau -- Message posted from http://www.ExcelForum.com I...

Too many Excel applications running at the same time
I have MS Excel 2010 installed on my computer. Every time I open up an Excel file it opens a new Excel application in my Windows dock at the bottom of the screen. If I open 5 Excel docs I have 5 separate Excel applications running. Before I had Excel 2010 installed I would only have one Excel app. running and I would see teh separate files in the Switch Window command at the top of the screen. What actions do I need to take so that I don't have numerous Excel apps running, instead of only one? I doubt if you have multiple Excel applications open. What you probably have is a button for e...

Calculation problem in Excel 2007
Hi, I have a sheet that has hundreds of Index, Match, Offset functions. It is not uncommon for the calculation order to break and the sheet to stall. After some digging around I found this on the Microsoft web site: http://support.microsoft.com/kb/919127. However, I've managed to reproduce the same problem in Excel 2007. Has anyone seen this in Excel 2007 too? Thanks, Schiz I have never been able to reproduce the problems mentioned in the KB article, even on massive workbooks. I would try Ctr/Alt/Shift/F9 once to rebuild the dependency tree to see if that fixes the problems f...

too many recipients #4
hello newsgroup I am using Outlook 2002 on XP. For a newsletter I have about 40 registred recipients. My problem is that after having send the letter I recieve a mail containing: Your message did not reach some or all of the intended recipients. Subject: [Deleted] Sent: 09.05.2004 22:09 The following recipient(s) could not be reached: name@account.com on 09.05.2004 22:09 xxx.xx.xx Too many recipients Can I change setting to avoid this failure? Thank You roli Try putting the names in the BCC field and address the item to yourself. Hopefully this won&...

Window's verson numbers
I'm writing an app in C++ using VS.net and I need to do this based on the OS. I can get the OS version number using _osver global variable found in the include STDLIB.H. But I need a list of what version number correspond to what OS so I know what the value in _osver means. Does anyone know where I can find these definitions? Also the major and minor builds might be useful? If this is the wrong newsgroup could you direct me to the right one? Thank Lots, Neil Neil B wrote: >I'm writing an app in C++ using VS.net and I need to do this based on the OS. >I can get the OS ver...

Running a macro for each item in an Autofilter list
I regularly produce a large sheet of data with about 15 fields/columns and several thousand rows on it which I currently filter by ones of the key fields (which may contain up to 50 different values) and then run a macro on each of those field selections (i.e. I manually run the same macro up to 50 times). How can I automate the filter selection so I can cycle through the macro as many times as required without any manual intervention. Thanks, I used column 1 as the key field in this shell: Option Explicit Sub testme01() Dim wks As Worksheet Dim myUniqueCells As Range Dim...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

One to many help
Using Access 2003, I am not certain how to approach this. I have two tables with a one-to-many relationship. The "many" table contains countermeasures related to a single problem detailed in the "one" table. These countermeasures have a status of either 'open' or 'closed'. I want to create a notification function when ALL countermeasures are closed, but each problem will have a different number of countermeasures. How can I set up the capability via a query (?) that lets me know when all countermeasures are closed. The c/m data is input into...

Excel gridlines
I have created a flowchart in excel and have removed the gridlines. When I save it in excel it appears the way that I would like it but when I cut and paste into a word document the gridlines reappear in the background. How do I remove them from the word document? -- pacususan Hi Pacususan, In Word, try: Table | Hide gridlines --- Regards, Norman "pacunurse" <pacunurse@discussions.microsoft.com> wrote in message news:44501C58-BB73-44BD-99E9-1FB377BF8684@microsoft.com... >I have created a flowchart in excel and have removed the gridlines. When I > save i...

FRx
After an upgrade of FRx (thin client version of 6.7 now), when users change from the default company, they still only see the default company reports listed. What is missing? Duh. Just a spec set issue. Company | Spec Set Setup. "Richard Rook" wrote: > After an upgrade of FRx (thin client version of 6.7 now), when users change > from the default company, they still only see the default company reports > listed. What is missing? ...

Make and use Invoices with Excel
Where would I find step-by-step insrtuctions on making invoices using the invoice template in Excel? Is there something specific you're having troubles with -- Message posted from http://www.ExcelForum.com Yes, I don't understand the process... I can modify the template and fill it in but don't know how to generate the 2nd invoice. Thanks for any help. Harry >-----Original Message----- >Is there something specific you're having troubles with ? > > >--- >Message posted from http://www.ExcelForum.com/ > >. > I am also having trouble linking t...

Displaying Time In Decimal Formats
I created a spreadsheet for making schedules, and am attempting to forc time to display in hours (as in 7.5 hours), instead of normal tim (7:30). I can't find a decent way of displaying it properly. Here's ho I have it set up currently: B5: Start_Time C5: End_Time B6: =IF(C5-B5>0.166666666666667;C5-B5-0.0208333333333333;C5-B5) The formula in B6 just checks to see if the scheduled shift is MOR than 4 hours long, and if so, subtracts half an hour from the shif (for a lunch break), and then displays the result. I would like th result to appear in hour format, as in 7.5, instead of t...

Formatting cells in Excel with Ascending/Descending order
I am trying to insert a drop down asceding/descending arrow in the cell of the excel document. When you click on the arrow which is a small box on the right side of the cell, it will organize all figures/data in that column and you can choose between ascending/descending, or how you want to organize it. Thank you -- Andrew ...

Line count
I am working with a program called Dictaphone. We use Word 2007 in this program. The line count on Dictaphone and the line count in Word 2007 are different even though the Dictaphone text is retrieved from Word 2007. Why would they be different? What is the difference between virutal line count and physical line count? ...

How do I open Excel 4.0 files in Office 2000?
I have Excel files with Macros, originally saved in Ver 4.0 (Office 97). Now running Office 2000 professional and when I try to open these files I just get a security warning and the files won't open. Hi have you tried setting the security level to 'Medium' ('Tools - Macros - Protection') "Tom_Hunn" wrote: > I have Excel files with Macros, originally saved in Ver 4.0 (Office 97). Now > running Office 2000 professional and when I try to open these files I just > get a security warning and the files won't open. ...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

group still appearing in GAL after change from Distribution to sec
Hi, I hope someone can help me out with this issue. I recently changed a group from a distribution group to a security group. I cannot see the group on the GAL but if I check the "member of" tab in outlook it still shows the group (even though it is now a security and not a distribution group) Any help would be appreciated. thanks what if you right-click the list, to to Exchange tasks, and either "remove email address" or "remove Exchange attributes"? -- Susan Conkey [MVP] "chrisr" <chrisr@discussions.microsoft.com> wrote in message ...

updating time once only when a cell is filled
Hi, Excuse my lack of experience with excel, I tried to get an answer googling for this problem I have, but I didn't succeed yet solving it. I'm trying to fill a form using excel, but I want that whenever a cell is filled, the time it was filled is updated. The problem I find is that time is updated in all the cells whenever a cell is change, do I make myself clear?, imagine I have these table: A B 1 +if(a1="","",now()) 2 +if(a2="","",now()) So initially I have: A B 1 2...