#### counting values in a column

```I have different values in a a column that I need to count.
Column A: 2,25,45,52,25,30,2,45,80,60
and I want to count values as follows
<30 = 4
>30 and <60 = 4
>60 = 2
I have tried countif and sumproduct but it doesn't work correctly.  Your
assistance is greatly appreciated.
```
 0
Utf
3/2/2010 3:08:03 PM
excel.worksheet.functions 4936 articles. 2 followers.

7 Replies
762 Views

Similar Articles

[PageSpeed] 19

```Set up a table in column B1:B4 like this:

0
29
59
999

where 999 represents a number which is surely higher than the highest value
in column A,

select range C1:C4 and enter this formula:
=FREQUENCY(\$A\$2:\$A\$11,\$B\$1:\$B\$4)
and confirm it with Ctrl+Shift+Enter (it's an array formula).

--
Regards!
Stefi

„Jerry” ezt írta:

> I have different values in a a column that I need to count.
> Column A: 2,25,45,52,25,30,2,45,80,60
> and I want to count values as follows
> <30 = 4
> >30 and <60 = 4
> >60 = 2
> I have tried countif and sumproduct but it doesn't work correctly.  Your
> assistance is greatly appreciated.
```
 0
Utf
3/2/2010 3:24:03 PM
```I can not do that because the other information is already sorted and if i
change my sorting will take me over two hours readjusting all other columns.

"Stefi" wrote:

> Set up a table in column B1:B4 like this:
>
> 0
> 29
> 59
> 999
>
> where 999 represents a number which is surely higher than the highest value
> in column A,
>
> select range C1:C4 and enter this formula:
> =FREQUENCY(\$A\$2:\$A\$11,\$B\$1:\$B\$4)
> and confirm it with Ctrl+Shift+Enter (it's an array formula).
>
>
>
> --
> Regards!
> Stefi
>
>
>
> „Jerry” ezt írta:
>
> > I have different values in a a column that I need to count.
> > Column A: 2,25,45,52,25,30,2,45,80,60
> > and I want to count values as follows
> > <30 = 4
> > >30 and <60 = 4
> > >60 = 2
> > I have tried countif and sumproduct but it doesn't work correctly.  Your
> > assistance is greatly appreciated.
```
 0
Utf
3/2/2010 3:38:01 PM
```please explain what is not working correctly
the only thing that I see that may be a problem is how you are
handling situations like = 30 or = 60
these situations will be excluded from your logic
if this is causing your problem you may just need to modify your starting or
ending points accordingly

good luck

"Jerry" wrote:

> I have different values in a a column that I need to count.
> Column A: 2,25,45,52,25,30,2,45,80,60
> and I want to count values as follows
> <30 = 4
> >30 and <60 = 4
> >60 = 2
> I have tried countif and sumproduct but it doesn't work correctly.  Your
> assistance is greatly appreciated.
```
 0
Utf
3/2/2010 3:48:01 PM
```Jerry wrote:
> I have different values in a a column that I need to count.
> Column A: 2,25,45,52,25,30,2,45,80,60
> and I want to count values as follows
> <30 = 4
>> 30 and <60 = 4
>> 60 = 2
> I have tried countif and sumproduct but it doesn't work correctly.  Your
> assistance is greatly appreciated.

One way:

B1=COUNTIF(A:A,"<30")

B2=COUNTIF(A:A,"<60")-B1

B3=COUNT(A:A)-SUM(B1:B2)
```
 0
Glenn
3/2/2010 4:02:19 PM
```I put =countif(an2:an240,"<31") and it works correctly for these set of
values or anything greater, in between values I have to do
=countif(an2:an40,"<61")-whatever value I had before so if I have to
breakdown into more groups i have to keep adding and substracting prior
values so that my in between values are a pain in the neck to keep of track

"pmartglass" wrote:

> please explain what is not working correctly
> the only thing that I see that may be a problem is how you are
> handling situations like = 30 or = 60
> these situations will be excluded from your logic
> if this is causing your problem you may just need to modify your starting or
> ending points accordingly
>
> good luck
>
>
> "Jerry" wrote:
>
> > I have different values in a a column that I need to count.
> > Column A: 2,25,45,52,25,30,2,45,80,60
> > and I want to count values as follows
> > <30 = 4
> > >30 and <60 = 4
> > >60 = 2
> > I have tried countif and sumproduct but it doesn't work correctly.  Your
> > assistance is greatly appreciated.
```
 0
Utf
3/2/2010 4:11:01 PM
```Then you want Stefi's solution.

Regards,
Fred

"Jerry" <Jerry@discussions.microsoft.com> wrote in message
news:0C72969F-D1A0-4EBC-8806-7788DF6DE4D0@microsoft.com...
>I put =countif(an2:an240,"<31") and it works correctly for these set of
> values or anything greater, in between values I have to do
> =countif(an2:an40,"<61")-whatever value I had before so if I have to
> breakdown into more groups i have to keep adding and substracting prior
> values so that my in between values are a pain in the neck to keep of
> track
>
> "pmartglass" wrote:
>
>> please explain what is not working correctly
>> the only thing that I see that may be a problem is how you are
>> handling situations like = 30 or = 60
>> these situations will be excluded from your logic
>> if this is causing your problem you may just need to modify your starting
>> or
>> ending points accordingly
>>
>> good luck
>>
>>
>> "Jerry" wrote:
>>
>> > I have different values in a a column that I need to count.
>> > Column A: 2,25,45,52,25,30,2,45,80,60
>> > and I want to count values as follows
>> > <30 = 4
>> > >30 and <60 = 4
>> > >60 = 2
>> > I have tried countif and sumproduct but it doesn't work correctly.
>> > Your
>> > assistance is greatly appreciated.

```
 0
Fred
3/2/2010 5:48:47 PM
```I think you're not understanding what pmartglass pointed out.

You posted these conditions:

<30 = 4
>30 and <60 = 4
>60 = 2

Less than 30
Greater than 30 and less than 60
Greater than 60

So, with those intervals you're not counting 30 or 60.

I'd use Stefi's suggestion but change the bins.

Let's assume your data is in the range A2:A11.

List the bins in C2:C4 -

C2 = 29
C3 = 59
C4 = >59

Those are the bins (conditions ) you need based on your posted results:

<30 = 4
>30 and <60 = 4
>60 = 2

Then, select the range D2:D4
Type this formula into the *formula bar*:

=FREQUENCY(A2:A11,C2:C3)

Do not hit Enter. Instead hold down both the CTRL key and the SHIFT key then
hit Enter.

--
Biff
Microsoft Excel MVP

"Jerry" <Jerry@discussions.microsoft.com> wrote in message
news:0C72969F-D1A0-4EBC-8806-7788DF6DE4D0@microsoft.com...
>I put =countif(an2:an240,"<31") and it works correctly for these set of
> values or anything greater, in between values I have to do
> =countif(an2:an40,"<61")-whatever value I had before so if I have to
> breakdown into more groups i have to keep adding and substracting prior
> values so that my in between values are a pain in the neck to keep of
> track
>
> "pmartglass" wrote:
>
>> please explain what is not working correctly
>> the only thing that I see that may be a problem is how you are
>> handling situations like = 30 or = 60
>> these situations will be excluded from your logic
>> if this is causing your problem you may just need to modify your starting
>> or
>> ending points accordingly
>>
>> good luck
>>
>>
>> "Jerry" wrote:
>>
>> > I have different values in a a column that I need to count.
>> > Column A: 2,25,45,52,25,30,2,45,80,60
>> > and I want to count values as follows
>> > <30 = 4
>> > >30 and <60 = 4
>> > >60 = 2
>> > I have tried countif and sumproduct but it doesn't work correctly.
>> > Your
>> > assistance is greatly appreciated.

```
 0
T
3/2/2010 6:08:58 PM

Similar Artilces:

Query not displaying correct column from a field
I have a table called Location with 2 columns, Abbr | Location VIL | Village I have a field on a form that is bound to the 2nd column of the table. I created a query based upon the form and have the location field in the query, when i run the query it displays the 1st column of the table. I need it to display the 2nd column. please adivise. Thanks Karl Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "thorpk@gmail.com&quo...

Hiding repeated values
I have a spreadsheet set up to track the outcome of various spread bet position. The profit/loss of each individual position is in column I, and in Column H I have a running total profit/loss using the formula =SUM(H4,I3)etc. My problem is that the formula extends to the bottom of the page, which I haven't filled up with positions yet, and so once it gets past the point where the individual totals run out, the same value repeats ad infinitum. Is there any way I can stop this, or at least hide the repeating values? I have managed this in my running average column using =IF(ISERROR(A...

Error retriving value from database
Hi I am trying to retrive a field from the database , say the quer returns me two rows with value 20.572 and 10.3 but when i get the value in the code is get it as 20.572 and 10.372. If I change the order of these rows and make it as 10.3 and 20.572 then i get the correct value 10.3 and 20.572. Can you let me know what might be causing this ? ...

Display result depending on value entered
I want a quick way to enter a time in a cell. example: F5 can either be 00:45 or 00:30 If the value 1 is entered into G5 then F5 will return 00:45 and if the value 2 is entered into G5 then F5 will return 00:30 what formula do I need for F5 Thank you, your help is appreciated. "Gotroots" <Gotroots@discussions.microsoft.com> wrote in message news:65A14660-7E8F-4801-96FD-F06BCAE5BF88@microsoft.com... >I want a quick way to enter a time in a cell. > > example: > > F5 can either be 00:45 or 00:30 > > If the value 1 is entered into ...

Value (Y) axis crosses between dates
I would appreciate it if someone could point me to a site where I can get information on the use of/thinking behind: 1. Value (Y) axis crosses between dates 2. Value (Y) axis crosses at maximum value that appears under 'Scale' in the 'Format Gridlines' dialog box. Thanks > 1. Value (Y) axis crosses between dates This means the axis crosses between dates (or categories) rather than at a date (or category). In a line or column chart, this means that a data marker or column is never split on the vertical axis. By default, Line and Column charts have this item ...

How to expand a collapsed column on excel worksheet?
I have two columns that I cant see (H and I). I want to see the value in them. How do I bring them back? EXCEL 2007 Click on G (cell header) and drag to J so that both columns are highlighted. Right click / Unhide. If my comments have helped please hit Yes. Thanks. "Rhona" wrote: > I have two columns that I cant see (H and I). I want to see the value in > them. How do I bring them back? ...

Secondary Row Group not displaying second value
I have a Line pivot chart that displays productivity as the y-axis over time in hours as the x-axis. It also has a secondary row grouping of the ID of the object being made. So my x-axis looks has the numbers 1-75 for duration in hours and then under that has a bracket that displays the number of the run. I'm changing the graph so that it shows 2 runs at a time and so displays 1-75 then 1-75 again for the second run and under that displays the run ID bracketing that runs data but the second run id is blanks even though its displayed in the pivot chart. For clarity I tried t...

How do I select noncontiguous columns? Select the first column/range ctrl-click on the subsequent columns/ranges. Barnsie wrote: > > How do I select noncontiguous columns? -- Dave Peterson CTRL + Click Gord Dibben MS Excel MVP On Tue, 2 Feb 2010 14:20:01 -0800, Barnsie <Barnsie@discussions.microsoft.com> wrote: >How do I select noncontiguous columns? Hold down the CTRL key when you select -- Max Singapore --- "Barnsie" wrote: > How do I select noncontiguous columns? ...

Modify POSH script to quit if null value is returned
The following article provides a POSH script to bulk enable agent proxy on systems within SCOM. The only problem I am having is that if a null value is returned then it bombs and goes into an infinite loop. How can I modify so that if a null value is returned the script just quits? For example when I have the script identify systems with a class of "Microsoft.Exchange.ServerRole.2003" and I don't have any Exchange 2003 servers in our environment then I get a message "Get-MonitoringObject : Cannot bind arguement to parameter 'Monitoring class' because...

Embedded Chart doesn't show data value on treadline.
I have an embedded chart that I input data on a monthly basis. This month it is not showing the data that I input. It shows that trendline but not the value. I've gone into the Format Data Series tab and don't see anything that is out of wack. Can any one help? Does the chart have the min. or max. value specified for either the x or the y axis? If so, that might result in the new value not being shown. Does the chart series include the cell(s) with the new values? Click the plotted series. Check the range reference in the formula bar. -- Regards, Tushar Mehta www.tushar-m...

making a column with working hyperlinks?
I was wondering if there is a setting to make it so the hyperlinks in a column would be real hyperlinks and not just text? I looked at formatting and did not see anything there. -- Robert Anderson Hi there, Ctrl + K will bring up the Insert Hyperlink dialog box. You can navigate there by going to the Insert (menu) | Hyperlink. Or you can make use of the HYPERLINK function. An example would look like this .. =HYPERLINK("http://www.vbaexpress.com/forum/","MyWebSite.com") -- Regards, Zack Barresse, aka firefytr "Robert Anderson" <nospam@nospamn...

Find Last Yaer's Value
I have a query having two columns. Date Sales 01/01/07 xxx 03/02/07 xxx Now I want to add one more column: Date Sales Sales(Before 364 days) 01/01/07 xxx yyy 03/02/07 xxx yyy How can I do this? JB Perhaps by using a subquery SELECT T.Date, T.Sales, (SELECT Sum(Sales) FROM TheTable as T2 WHERE T2.Date Between DateAdd("d",-364,T.Date) and T.Date) as OldSales FROM TheTable as T Or by using the DSUM function Field: DSum("Sales","TheTableName","Date between #" & DateAdd("d",-364,TheTableName.Dat...

Conditional count of dates before deadline
I am in the process of preparing compliance reports for a review cycle. An important piece of information is whether or not compliance was reached within 1 year of the audit. In the dB I have a range of dates when evidence of compliance was submitted for each entity. What I would like to do is get a count of compliance completed within the year for each entity. I have tried count if and IIF statements without any success. I was able to successfully apply conditional formatting to those dates that were > 1 yr due date. Thanks in advance for your thoughts Could you share some fiel...

Add text automatically by recognized value
I'm trying to create a formula where it recognizes the first three digits of an item number at the same time entering the department name in the cell next to it. So let's say the item number R33569 for dept. WRP, I would only need it to recognize the R33 while entering WRP in the column next to the item number. This is what I got on my work sheet Column C Item Number R33569 R33456 R33447 In the column to the right or left doesn't matter which I need to say the dept. name. So how can i do that without having go through the hundreds of partnumbers that I have. ...

displaying data within column
hi there i'm having alot of trouble trying to display data within column. here is a screenshot of what i am doing: '[img=http://img363.imageshack.us/img363/6720/graph2vr.th.jpg] (http://img363.imageshack.us/my.php?image=graph2vr.jpg) the x axis is phase so each column in the graph are phases. the y axi is time - how long each phase lasts. but within each of the phases (columns) i would like to display data the final data column in the spreadsheet - "% of cost in phase". so th phase (column) is broken down into its deliverables (the 2nd data colum in spreadsheet) accordi...

Sumproduct where cell values are the same in a sorted column
In cell C2 I have the following formula that works fine for what I need it for. =SUMPRODUCT((\$L1:\$L23<\$B1)*(\$M1:\$M23<=\$A\$1)*(\$L13:\$L23>=\$K1)) In the cells in column J i have a list of numbers that are the same in the following different ranges as follows J2:J23 = 123456 J24:J30 = 123457 J31 = 123458 J32:J33 = 123459 etc. I copy the formula in cell C2 down the column and then I have to manually change the range in the formula to match the ranges of other columns. So the following cells will have the following formulas in. The rest of the cells in the range are ...

pasting values from pivot table still linked
I have a fairly big table that provides values for a pivot table. I would like to view the data from the pivot table and copy and paste this information to a new tab as values, and retain the formatting. The goal is to keep the spreadsheet from freezing - which it seems to do too much. I understand that pivot tables share cached memory. This copy, paste special, values and paste special "all using source theme" method seemed to be working. However, I noticed tha the sheet was freezing more and more and today, I noticed that when I updated the pivot table for t...

Transaction count
Other than using my Z and ZZ reports, is there a report that I can run that will give me the number of transactions over a period of time? I would like to use this information to determine our items per transaction and also dollars per transaction for any give day, week, month or year. -- Kris hi Kris, There is on report called SO Manager> Utilities> Crystal Report > RegAnalys.def which may help you else send me the specs I do for you free. Regards Akber "Kris" wrote: > Other than using my Z and ZZ reports, is there a report that I can run that > will give m...

Lookup
Hi I have a field in contact form called Id. I am looking up the contact on the campaign response form and want to populate the Id of the contact selected in a field on the campaign response form called CId, when the particular contact is looked up. Any ideas how i could go about this please? Any help would be appreciated - thanks heaps in advance Regards Ridhima You will need to fetch the contact id "on change" of the contact lookup field in your campaign response form using javascript. Then use this contactid value to fetch the id field for the contact (using ajax to call...

Move rows into columns
I have an Excel sheet that has 8000+ rows that include contact information. I'd like to move them from all of those rows into 6 columns. Currently it lookzs like: name title company address city/state/zip phone .... .... .... And I'd like to create a formula to move it to 6 columns that look like this: name title company address city/state/zip phone I'm still an Excel novice but would appreciate any suggestions you might have. Thanks! You will need VBA code to get this done. If you would like I could whip up a code for you if yo...

value for a cell
Hi, I have one excel sheet the value for which is displaying is 192.32 bu cell value for it =RONE. I am unable to link the value wih 192.32 wit the name RONE. Is it a variable or something? PLease help out in thi regard? Ashis -- ashishpre ----------------------------------------------------------------------- ashishprem's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3148 View this thread: http://www.excelforum.com/showthread.php?threadid=57406 Ashish, It is probably a name. Goto Insert>Name>Define... and see if it is in that list. -- HTH Bob Phi...

Excell: Column Changes
The Excell program changed the Column labels from alpha characters to numbers. This makes it somewhat difficult to use formulas. Any ideas on how to changes back the numbers to alphabet characters ??? Tools|options|General Tab|Uncheck R1C1 reference style Doug wrote: > > The Excell program changed the Column labels from alpha characters to numbers. This makes it somewhat difficult to use formulas. Any ideas on how to changes back the numbers to alphabet characters ??? -- Dave Peterson ec35720@msn.com Tools -- Options. On the View tab, under Settings, uncheck R1C1 style. Rgds, And...

Problems charting x axis values
Ok, lets say I have a dates (3/14/94) in columns a2 and a25 and (3/15/04) in columns a27 and a50 and times (0:00) in columns b2 and (11:00) in b25 and the same time in columns b27 and b50. The problem is when I type in the category x axis labels =sheet2!\$a2:\$b50 it will only label the first date in columns a2 and a27 and not the ones in a25 and a50. ----------------------------------------------------------- x axis 0:00 0:00 Does this 3/14/94 3/15/04 -----------------------------------------------...

Inventory Count #2
I use my laptop for back office and I need to count invetory. the problem is my laptop dont have long range and It is disconnecting from router. Is there any other way to count inventory and import to rms. -- Thank You Harjit SherGill /DBA Altaville Market 324 South Main st po box 370 Altaville CA 95221 altavillemarket@att.net -- Thank You Harjit SherGill /DBA Altaville Market 324 South Main st po box 370 Altaville CA 95221 altavillemarket@att.net Gill, If you are interested we can offer a custom excel interface solution, where you can transfer the inventory data file into excel sh...

Default column width opeing *.csv in Excel
How do you set default column width when opening a *.csv in Excel? Changing files in \XlStart directory did not help. -nn As it is a csv you will only be able to do this in code. If it is the same file layout each time, why not record a macro in your personal.xls file and run this each time -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "Netnathan" <n.a.smith@att-nospam.net> wrote in message news:%23NpbeF9FHHA.4588@TK2MSFTNGP05.phx.gbl... > How do you set default column width when opening a...