Count numbers in list

A

Arne Hegefors

Hi! I have a long list of numbers ranging from -100 to 100. The list contains
many thousands of numbers. I want to count the number of cells containing eg
the number 11. How can I do this?
 
R

Ron Coderre

Try this:

With
A1:A1000 containing various numbers

This formula returns the count of cells with a value of 11

B1: =COUNTIF(A1:A1000,11)

OR....you could enter 11 in cell C1 and use
B1: =COUNTIF(A1:A1000,C1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
A

Arne Hegefors

Thanks! However since the numbers range from -100 to 100 I want to group them
first. Lets say i want to group the numbers in 10s. how can i do that? help
very much appreciated!

"Ron Coderre" skrev:
 
R

Ron Coderre

Then....Try this:

With
A1: MyValues (the range must have a column heading)
A2:A100 containing various numbers

Select A1:A100

<Data><Pivot Table>
Use: Excel
Select the data range (a1:A100)
Click the [Layout] button

ROW: Drag the MyValues field here
DATA: Drag the MyValues field here, too
If it doesn't list as Count of MyValues...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table to create the Pivot Table...but, we're
not done yet.

Next.....
Right-Click on the "MyValues" heading
Select "Group and show detail" from the shortcut menu
Select "Group"
Change the "Starting At" and "Ending At" settings, if necessary
Set "By" to 10
Click [OK]

Now the Pivot Table will display something like this:
Count of MyValues
MyValues Total
-100--91 4
-90--81 3
-80--71 3
-70--61 4
-60--51 3
-50--41 3
etc

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
V

vezerid

Arne,

You can use a rounding function. FLOOR(x, 10) will return 0 for all
numbers in [0, 10), 10 for all in [10, 20) etc.

=SUMPRODUCT(--(FLOOR(A1:A1000,10)=10))

HTH
Kostis Vezerides
 
A

Arne Hegefors

Thanks alot! That was a very generous answer!!!

"Ron Coderre" skrev:
Then....Try this:

With
A1: MyValues (the range must have a column heading)
A2:A100 containing various numbers

Select A1:A100

<Data><Pivot Table>
Use: Excel
Select the data range (a1:A100)
Click the [Layout] button

ROW: Drag the MyValues field here
DATA: Drag the MyValues field here, too
If it doesn't list as Count of MyValues...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table to create the Pivot Table...but, we're
not done yet.

Next.....
Right-Click on the "MyValues" heading
Select "Group and show detail" from the shortcut menu
Select "Group"
Change the "Starting At" and "Ending At" settings, if necessary
Set "By" to 10
Click [OK]

Now the Pivot Table will display something like this:
Count of MyValues
MyValues Total
-100--91 4
-90--81 3
-80--71 3
-70--61 4
-60--51 3
-50--41 3
etc

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


Arne Hegefors said:
Thanks! However since the numbers range from -100 to 100 I want to group them
first. Lets say i want to group the numbers in 10s. how can i do that? help
very much appreciated!

"Ron Coderre" skrev:
Click to expand...
Click to expand...
 
R

Ron Coderre

Thanks for the feedback, Arne.....I'm glad I could help.

***********
Regards,
Ron

XL2002, WinXP


Arne Hegefors said:
Thanks alot! That was a very generous answer!!!

"Ron Coderre" skrev:
Then....Try this:

With
A1: MyValues (the range must have a column heading)
A2:A100 containing various numbers

Select A1:A100

<Data><Pivot Table>
Use: Excel
Select the data range (a1:A100)
Click the [Layout] button

ROW: Drag the MyValues field here
DATA: Drag the MyValues field here, too
If it doesn't list as Count of MyValues...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table to create the Pivot Table...but, we're
not done yet.

Next.....
Right-Click on the "MyValues" heading
Select "Group and show detail" from the shortcut menu
Select "Group"
Change the "Starting At" and "Ending At" settings, if necessary
Set "By" to 10
Click [OK]

Now the Pivot Table will display something like this:
Count of MyValues
MyValues Total
-100--91 4
-90--81 3
-80--71 3
-70--61 4
-60--51 3
-50--41 3
etc

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


Arne Hegefors said:
Thanks! However since the numbers range from -100 to 100 I want to group them
first. Lets say i want to group the numbers in 10s. how can i do that? help
very much appreciated!

"Ron Coderre" skrev:

Try this:

With
A1:A1000 containing various numbers

This formula returns the count of cells with a value of 11

B1: =COUNTIF(A1:A1000,11)

OR....you could enter 11 in cell C1 and use
B1: =COUNTIF(A1:A1000,C1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Hi! I have a long list of numbers ranging from -100 to 100. The list contains
many thousands of numbers. I want to count the number of cells containing eg
the number 11. How can I do this?
Click to expand...
Click to expand...
Click to expand...
 
Top