Count numbers in list
 

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?

Count numbers in list
 

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


"Arne Hegefors" wrote:

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?

Count numbers in list
 

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


"Arne Hegefors" wrote:

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?

Count numbers in list
 

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" wrote:

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


"Arne Hegefors" wrote:

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?

Count numbers in list
 

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

On Feb 13, 7:24 pm, Arne Hegefors
wrote:
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

"Arne Hegefors" wrote:

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?

Count numbers in list
 

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" wrote:

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


"Arne Hegefors" wrote:

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?

Count numbers in list
 

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

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

XL2002, WinXP


"Arne Hegefors" wrote:

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" wrote:

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


"Arne Hegefors" wrote:

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?