Formula/Macro to tally up numbers in Excel

I have a list of numbers in a column, e.g. :-

1
4
6
1
35
14
15
15
2
1
6
6
1
19
15
5

and want the computer to work out for me, how many times number x has
turned up in column B?

I presume this is possible with a function/macro within excel fairly
easily? Any advice/pointers in the right direction would be
appreciated.

Please prefably reply in the newsgroup, or via email to
"newshelp@the-core.freeserve.co.uk.DELETE", removing the .DELETE from
the end of my address.


Thanks, Mark Pyne
0
5/7/2004 4:26:22 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
580 Views

Similar Articles

[PageSpeed] 38

Hi

Have a look at COUNTIF() and try something like:
=COUNTIF(B:B,1) to count how many 1s there are etc

-- 
Andy.


"Mark" <private@nospam.com> wrote in message
news:fndn90d73bcvr94h3i9edifm2tpfh5rldk@4ax.com...
>
> I have a list of numbers in a column, e.g. :-
>
> 1
> 4
> 6
> 1
> 35
> 14
> 15
> 15
> 2
> 1
> 6
> 6
> 1
> 19
> 15
> 5
>
> and want the computer to work out for me, how many times number x has
> turned up in column B?
>
> I presume this is possible with a function/macro within excel fairly
> easily? Any advice/pointers in the right direction would be
> appreciated.
>
> Please prefably reply in the newsgroup, or via email to
> "newshelp@the-core.freeserve.co.uk.DELETE", removing the .DELETE from
> the end of my address.
>
>
> Thanks, Mark Pyne


0
andyb1 (494)
5/7/2004 4:30:56 PM
On Fri, 7 May 2004 17:30:56 +0100, "Andy B"
<andyb@takethisbitout.dawsons.co.uk> wrote:

>Hi
>
>Have a look at COUNTIF() and try something like:
>=COUNTIF(B:B,1) to count how many 1s there are etc

Works perfectly for my task, many thanks ! :-)

Mark Pyne
0
5/7/2004 4:52:48 PM
Reply:

Similar Artilces:

Macro Upon Opening Then Close
I am hoping to create a macro that upon opening a file it will do the following: 1)Copy A1:B:2 (just as an example; also - there is only one worksheet in this file) 2)Activate Workbook "Price Chart Template" (only 1 sheet here too) 3)Paste on A:1 4)Go back to the original document and close it without saving (this is where I'm wondering if code can allow to close the document it is written in???) Thank you. -- David P. Workbooks("name1").Sheets(1).Range("a1:B2").Copy Workbooks("name2").Sheets(1).Range("a1").paste Workbooks("nam...

Deleting Macros
Having created a couple of macros within a workbook for a particular topic and then deleting them when they were no longer required, I still get the "Disable - Enable" Macros prompt when loading the spreadsheet. As there are no macros associated with this particular spreadsheet can anyone tell me why it still appears? And how can I prevent it from appearing each time I open the spreadsheet. PS. I have also noticed this point previously when simply copying a worksheet from a workbook containing macros to a new workbook without any macros. Thanks in advance JohnH You also have...

help with formula please #2
Can anyone please help with this formula. If Sheet1 Column A = nothing(blank) And Column B = \\\\\\CC\\\\\\ Then Sheet 2 B7 = SumTotal. As a regular formula would be nice or VB code Hi Richard you want nothing at all in the whole of column A in sheet 1? do you want every cell in column B of sheet 1 to have \\\\\\CC\\\\\\ or should this (can this) only appear in one cell? and what range are you summing on sheet 2 in cell B7? Cheers JulieD "Richard" <anonymous@discussions.microsoft.com> wrote in message news:82ce01c477e1$77f0e740$a301280a@phx.gbl... > Can anyone please ...

How to run Visual Basic from Excel sheet #8
How do I run a Visual Basic routine from a normal Excel cell. I would like to do something like this: =IF(RunIt=TRUE, Run my Visual Basic routine, Do nothing) So if some calculations results in the variable "RunIt" is set equal to TRUE, the Visual Basic routine must run. Kristian ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi Kristian, You can only run functions from a worksheet. Not subs. You can use a worksheet change event. Do a google search for w...

Appending or Up-dating a formula
I am using an =Ave function in a formula to average quite a few cell values together. I add new information all the time; sometimes 5 or 6 new cells at a time. How can I append or up-date my formula to include these new cells with out have to type the new cells into the formula? When I make the cell that has the formula in it active/edit, all of the cells that are referenced in the formula have a highlight around them. Is there a key or key combination I can press while clicking on the cells I want to add to the formula? Thanks, john Hi see your post in Excel.misc -- Regards Frank ...

Changing a range of an array in a SUMPRODUCT formula gives a #N/A error
I have a working SUMPRODUCT formula {=SUMPRODUCT(($C$4:$C$4341=z8)*(audittypefm1="Standard"))} that I need to change the 1st array to C5623. When I do I get a #N/A error. I've tried giving the range a name (empnofm2), and I've verified all the data in the C4:C5623 range is formated the same (general, it is all employee numbers from 2 to 5 digits long. I'm using a "trim all" macro which is working great. I am unable to determine what is causing the #N/A and how to fix it. Basically I copied an entire worksheet within the same workbook and changed the name, wh...

Excel programming
Hi All! Here is some Help asked, since I'm not familiar with Excel programming, I can't find an Answer for my problem. Here is the Problem: I need to replace come words in 1st.Cell with words from another 3rd.Cell if Content of the 1.st.Cell and the 2nd.Cells matches. Main problem is the Volume 25.000 Strings ? 15 Rows. As I understand the algorithm for this operation it may look like this: Get Ai Value Get Bj Value Compare Ai and Bj values for equality If true then get Cj Value and put to Ai Value; Next Ai (Ai++) If false then get next Bj (Bj++) Example: Before replaceme...

Is there a virus out there that is affecting excel spreadsheets
Was adding my spreadsheets and the numbers would not add up. So I shut my computer down and ran a norton anti virus program. It seemed to correct the problem for now but can someone advise me if there is a virus out there that is affecting the spreadsheet programs Thanks Jean "jean" <jean@discussions.microsoft.com> wrote in message news:E371C861-DCB6-4B4F-AF08-335133C560EB@microsoft.com... > > Was adding my spreadsheets and the numbers would not add up. So I > shut my computer down and ran a norton anti virus program. It seemed > to correct the problem for n...

help with a formula #5
I have 98 sets of magazines each set has 12 in the set what formula can I use to get a total of issues in all sets have you tried 98*12 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Just Me" <no@isp.com> wrote in message news:eZ66ZEGHGHA.1192@TK2MSFTNGP11.phx.gbl... >I have 98 sets of magazines each set has 12 in the set > > what formula can I use to get a total of issues in all sets > =98*12 or =A1*A2 if the numbers are in A1 and A2 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Just Me" <n...

Formula #29
Hi, What formula should I use if I want it to look at a specific cell and then work out the total number between two numbers. e.g. if cell H6 had 7-4 entered the number 9 would be returned. Thanks in advance I'm not sure how you got 9, but your life would be much easier if you used two separate cells and then subtracted the smaller from the larger (and then added one???). Boenerge wrote: > > Hi, > What formula should I use if I want it to look at a specific cell and then > work out the total number between two numbers. > e.g. if cell H6 had 7-4 entered the number 9 woul...

Can offset be used in this formula?
Can the offset be used in the below formula for the information in column "K"? In column L have the the following formula's L46 =MAX($I46*1000-$K$46*$J46,0) L47 =MAX($I47*1000-$K$46*$J47,0) .... L99 =MAX($I99*1000-$K$46*$J99,0) What I'd like to do is to copy the fomula into columns M, N, O, ... M47 =MAX($I47*1000-$K$47*$J47,0) M48 =MAX($I48*1000-$K$47*$J48,0) M49 =MAX($I49*1000-$K$47*$J49,0) .... N48 =MAX($I48*1000-$K$48*$J48,0) N49 =MAX($I49*1000-$K$48*$J49,0) .... O50 =MAX($I50*1000-$K$50*$J50,0) O51 =MAX($I51*1000-$K$50*$J51,0) .... How abou...

Can I Increase The Length Of The Serial Number?
Is it possible to increase the length of the serial number field? We use some very long serials and the software will not accept the whole of the serial number. Thanks. You can try testing on a demo database but I am pretty sure that RMS will not like your changes. I know from experience that if you did the same thing with the ILC or Description fields this will cause RMS to crash. Rob "El Preisdente" wrote: > Is it possible to increase the length of the serial number field? > > We use some very long serials and the software will not accept the whole of > the s...

Formula to return tomorrow's date.
I have a report that must be turned in the night before for tomorrow's date. Is ithere a date formula that will return tomorrow's date to a cell in Excel? One way: =TODAY() + 1 In article <668782AC-774A-475F-9821-8A4C3B14A983@microsoft.com>, "Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote: > I have a report that must be turned in the night before for tomorrow's date. > Is ithere a date formula that will return tomorrow's date to a cell in Excel? I assume that you want the date to remain static after entry. Easiest way, use 2...

calculate per day usage with a number range
I have the check in date and the check out date for a large group. I would like to see how many rooms are used per day for the period. Column A = Check in date (ex: 2-feb) Column B = Check out date (ex: 10-feb) How can I tell how many rooms were used on 6-feb, how many on 7-feb, etc? Thanks so much for your help! Put the date of interest (e.g. 6-feb-2008) in D2 then in E2 use this formula =SUMPRODUCT((A$2:A$100<=D2)*(B$2:B$100>=D2)) You can copy the formula down to calculate for more date in column D "peabody" wrote: > I have the check in date and the check out dat...

COM in VB Excel
How do you register a stand alone COM object in a VB macro? ...

Weekday formula
Can anyone help with the weekday formula. What I want to do is enter the date in say A1 11/20/2003 and have B return the answer of Thursday. Currently I use the weekday formula bu only returns the number of the day. Thanks in advance B ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com There are several ways to do this. One is to enter =A1 and format the cell with a custom number format of dddd. Another way is to use the function =TEXT(A1,"dddd"). -- Cordiall...

To find out number of sundays and saturdays in january month
Hai all, How I can find out the number of saturdays and sundays in January For example in A1 and B1 cell represents Saturdays and Sundays. How I can find out the number of saturdays and sundays in that month and to write in corresponsing A2 and B2 column . Please help with thanks and regards Pol =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=1)) Would tell you the number of Sundays in January of 2008. =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=7)) Would tell you the number of Saturd...

Count formula within a named range.
Hi, How do I change the following formula =SUMIF($F$39:$F$79,"PW Shopfitters",$D$39:$D$79)/COUNTIF($F$39:$F$79,"PW Shopfitters") to count within a named range (PW Shopfitters). Any help would be great. Cheers, Phil Hi A range name cannot have spaces, maybe that's your problem. Name your range PW_Shopfitters. Then replace your cell references with PW_Shopfitters, eg =Sumif(PW_Shopfitters,"PW Shopfitters",............ -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "PW11111" wrote: > > Hi, > &g...

Add data to excel series
I receive daily API transation data. Each day the data is provided via a text file. Data is in columns. First column is date, second column is API (35 different APIs), third column is transaction volume and fourth column is average response time. I would like to create a chart with date on the x axis, transaction volume on the y axis, and each API as its own series. I can create a base chart for a single day with all series. I am having a problems adding in addition series data for each additional day? Is there an easy way to add data to each series? Create a PivotTable (and PivotC...

Copy macro to other row
Hi all, I've created two macron that executes calculations on a certain row. Can I copy these macros so that they can execute the same calculations but on a another row i.e. only on one row at the time leaving the others unaffected. Regards Dan As always, post your coding efforts for comments -- Don Guillett SalesAid Software dguillett1@austin.rr.com "togge" <dantorgny@spray.se> wrote in message news:1158919664.025298.246070@b28g2000cwb.googlegroups.com... > Hi all, > > I've created two macron that executes calculations on a certain row. > Can I cop...

Formula to return a formula
At least I think that's what I'm looking for. here's my situation: I've got a sheet where there is a variable (X) that changes depending on what row it is on. So I have a column (column c) that lists these variables (X is dependant on things from a different sheet). X is a muliplier that is utilized differently depending on the value of N. N is a Picklist selection and can change periodically. Or even be duplicated on more than one row. I have a vlookup that checks the value of N (column a) and needs to return a formula that has X applied if needed on the approp...

Random number generator expression used
My final year (Statistics degree) project is concerned with the PRNG in MS Excel. It was stated by the lecturer in Simulation Studies that this PRNG was no good for simulation purposes .. the cycle length is not long enough. So that I can test this statement I need to be able to run the in-built PRNG so that it repeats the sequence of generated random numbers. This cannot be done in situ as there are not enough cells available (max 16k ?). I would propose to run the PRNG as a VBA macro - or stand_alone in VB6 - but for that I need to obtain the mathematical expression that is used ...

How to use a text formula as code formula
Hello. I have this formula loaded from a text field on a form: val([field10])+val([field20]) and I would like to use this as a code formula. I mean, if I use that in VBA code it works, but If I load from a text field it doesn't work. I need that my form use that formula stored in a text field to calculate values. Regards in advance, Marco responded to in another newsgroup. It's rarely necessary to post the same question to more than one group. If it is necessary, select all the relevant newsgroups in the "To:" or "Newsgroups:" field. That way, folks who...

Heading numbers in the left margin
Hi, I'm trying to create outline numbered headings, where the number is located in the left margin. I try to accomplish this using a negative value for "NumberPosition" on the ListTemplate. Initially, this works fine, but as soon as I click on the Heading's number, the whole paragraph, including the number, "jumps in" to the right so that the number is located exactly at the margin instead of to the left of it. I define my styles and ListTemplate in VBA, the relevant portion of which reads: With .ListTemplate.ListLevels(1) .NumberPo...

Word Table into Excel
When I copy a row of text (divided into a number of columns) from a wor table and paste in Excel - it copies down all information from acros the columns down into ONE column (col A) instead of across int seperate columns. Is there any way I can copy over details from table in word to cells into Excel without writing VB -- Message posted from http://www.ExcelForum.com you can create a table in Word that is the same size as the excel information that you are trying to move and then simply copy and paste (when pasting make sure that you highlight the entire table though.) >-----Origi...