How do I calculate how many instances of a number appear in a wksh

  • Thread starter Thread starter JC in Aus
  • Start date Start date
J

JC in Aus

For instance, if I have a worksheet which has 3000 cells with difference
numbers in each, how can i report on how many cells contain the number 11,
and so on?

I want to report on around 50 numbers, and show how many times all 50
numbers appear in the worksheet in some sort of report.

Would really appreciate it if someone could help.
 
=COUNTIF(A1:E600,11)

for example

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Let's say the worksheet containing the data is called data. In another
worksheet list your desired values in column A in cells A1 thru A50. In B1
enter:

=COUNTIF(data!$1:$65536,A1) and copy down. Adjust the 65536 if you are
using 2007.
 
If you have a separate list of the 50 numbers to count you could use the
FREQUENCY function.

You will need a data_array(the column of 3000 number), a bins_array(the column
of 50 numbers).

Assume the data is in Column A and the bins_array is B1:B50

Select C1:C50 and in C1 type =FREQUENCY(A1:A3000,B1:B50)

CTRL + SHIFT + ENTER


Gord Dibben MS Excel MVP

On Wed, 2 Jan 2008 02:23:00 -0800, JC in Aus <JC in
 
Back
Top